PHP Database Connection with MySQL Database

Here you will learn how to make PHP Database Connection with MySQL Database in all possible ways at the Localhost. Instead of the MySQL DB, you can use the MariaDB. And in the end, you will also learn how to close the MySQL Database Connection.

Create a Database

First, we need to create a Database to make a PHP database connection with the Database. But, if you don’t have the localhost in your PC, you can use the XAMPP software to make the local server.

Follow the below steps to create a Database

Step-1: Open the XAMPP application and start Apache and MySQL Server.

Step-2: Go to the phpMyAdmin and create a new Database

After starting the Apache and MySQL server, Open your browser and type the following URL to open the phpMyAdmin Dashboard.

http://localhost/phpmyadmin/

Now on the left side of the phpMyAdmin Dashboard, click on the New to create a new database.

Step-3: Enter the database name and choose the collation

After that, enter the database name and choose the collation, and then click on the Create button.

The database name is totally up to you. But, here I named the database my_test_db and the collation is utf8mb4_unicode_ci. If you wish, you can enter the same I entered.

---

Writing PHP Code for Making MySQL Database Connection

After creating the my_test_db Database, now we have to write the PHP code for making a connection to the Database.

In PHP, there are few ways to write codes such as – POP, OOP, and PDO. And in this tutorial, we will make the PHP Database connection in all the ways.

✍ MySQLi Procedural (POP) way to Connect Database in PHP

In the Procedural method, we will use the mysqli_connect() function to create the Database connection.

And we will use the mysqli_connect_errno() function to confirm that the database is successfully connected.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$db_connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

// CHECKING THE DATABASE CONNECTION
if(mysqli_connect_errno()){
    echo "Connection Failed".mysqli_connect_error();
    exit;
}
else{
    echo "The database is successfully connected";
}
?>

✍ MySQLi Object-Oriented (OOP) way to Connect Database

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$db_connection = new mysqli($db_host, $db_user, $db_password, $db_name);
// CHECK DATABASE CONNECTION
if($db_connection->error){
    echo "Connection Failed - ".$db_connection->connect_error;
    exit;
}
else{
    echo "The database is successfully connected";
}
?>

✍ PHP Data Objects (PDO) way to Connect Database

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

// DSN(Database Source Name)
$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";

try{
   $db_connection = new PDO($dsn, $db_user, $db_password);
   // SET THE PDO ERROR MODE TO EXCEPTION
   $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e){
   echo "Connection failed - ".$e->getMessage();
}
?>

---

Should You Use MySQLi or PDO?

PDO supports 12 different database systems, whereas MySQLi only supports the MySQL databases.

If you only work with the MySQL Database, then you can choose whatever you like. And in other cases, you have to use the PDO.

Both are object-oriented and both support the Prepared Statements that will protect your code from SQL injection.

But MySQLi also offers a procedural API. The PHP MySQLi Procedural is easy to learn and easy to use, and it is good for tiny projects. But it would be stupid to use this in large projects.

---

Close the Database Connection

The PHP database connection will be closed automatically when the script ends. But if you want to close it earlier, PHP also allows you to close the DB connection at any time.

MySQLi Procedural way to close DB connection

In the MySQLi Procedural way, you have to use the mysqli_close() function to close the database connection.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$db_connection = new mysqli($db_host, $db_user, $db_password, $db_name);

// CLOSING THE DB CONNECTION
mysqli_close($db_connection );
?>

MySQLi Object-Oriented way to close DB connection

In the MySQLi OOPS, you have to call the close() method to close the connection.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$db_connection = new mysqli($db_host, $db_user, $db_password, $db_name);

// CLOSING THE DB CONNECTION
$db_connection->close();
?>

PHP PDO Close DB Connection

To close the DB connection in PHP PDO, you have to set the connection variable into null, or you can use the unset() function to unset the connection variable.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";

try{
   $db_connection = new PDO($dsn, $db_user, $db_password);
   $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e){
   echo "Connection failed - ".$e->getMessage();
}

// CLOSING THE DB CONNECTION
$db_connection = null;
// OR
unset($db_connection);
?>