Best methods for PHP MySQL Connection?

Today I am going to discuss how we can do PHP MySQL connection in PHP application.

Introduction

PHP MySQL connection combination is very popular and widely used. PHP based CMS like WordPress, Joomla, Drupal, Moodle and any other PHP based framework almost use MySQL database.

So to use MySQL database with PHP, we need to connect it with PHP application. There are 2 methods two connect MySQL database with PHP.

• MySQLi extension (Improved version of MySql extension)
• PDO (PHP Data Objects)

Note– Earlier versions of PHP used the MySQL extension. But this extension was deprecated in 2012.

So I will not recommend you to use this as it has man security flaws. And if in your old PHP application or website, you are using MySQL() extension please correct it asap.

MySQLi extension

Installation

MySQLi extension is auto-installed on Linux and Windows servers where PHP MySQL package is installed. So we don’t require any installation for MySQLi.

Database Connection

I am going to share a sample code for PHP MySQL connection with PHP.

I am not showing here, how to create a database in MySQL.

Lets first create a dbConnection.php file where we will create a separate database connection. This is a good practice to have a separate DB connection file so that we can use it wherever required in the project.

Otherwise, you have to write again and again the same database connection code in each file.

You just have to include it by using PHP custom function include (include ‘dbConnection.php’) on the top of each PHP file and we can its function to use it.

<?php
/* 
*Lets write a function for database connection
*/
function DbConnect()
 {
    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "";
    $db = "sample";
    $conn = new mysqli($dbhost, $dbuser, $dbpass,$db) or die("Connect failed: %s\n". $conn -> error);
 
   return $conn;
 }
 // DB close connection
function CloseDbConnect($conn)
 {
   $conn -> close();
 }
   

In the above code, mysqli is the function that takes parameters and creates a database connection.

$conn = new mysqli($dbhost, $dbuser, $dbpass,$db)

• $dbhost is the host whee your server is running like loclahost
• $dbuser is the user for the database.
• $dbpass is password for the database.
• $db is the name of the database that we are going to connect.

Check Database Connection

Now we will check the database connection that we created in the above section (dbConnection.php).

Let’s create a new PHP file test.php and include database connection file (include ‘dbConnection.php’) to open database connection.

<?php
include 'dbConnection.php';
$connection = DbConnect();
echo "Connected Successfully";
CloseDbConnect($connection);
?>

Now, lets execute this test.php file in the browser. It will print a success message “Connected Successfully”.

PDO (PHP Data Object)

PDO is enabled by default on PHP 5.1 + versions. If it’s not then please visit the installation guide.

Let’s create a dbConnection.php file using PDO to connect MySQL database with PHP code.

<?php
/* 
*Lets define database properties 
*/

    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "";
    $db = "sample";
    try {
    $conn = new PDO("mysql:host=$dbhost;dbname=$db",$dbuser,$dbpass);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Above PDO database connection file can be used in any PHP file to initiate database connection.

PDO Close Database Connection

$conn = null;

MySQLi Vs PDO – Which one to Use

You can MySQLi and PDO both, which one you are more comfortable with. The main differecn is PDO can work wit 12 diferent datbase while MySQLi only workds with MySQL.

So while switching the database in your project with PDO is very easy while in case of MySQLi you will have to rewrite entire code and query.

Both support object oriented, prepared statements and good for web application security.

Please share your thoughts friends, what do you recommend PDO or MySQLi for PHP MySQL connection?