Nowadays, PHP have several database extensions. In the past, there was only the MySQL extension. Later, MySQLi extension appeared. It enabled developers to write more neat and maintainable code. It had also the power of object-oriented and it was more secure and strongly recommended in its days. Now, we have PDO that literally has it all.

MySQL

Most of us started learning PHP by using this extension. But if some developer decided to go on using this driver, he will be in a big risk.

First, it’s outdated and not secure. PHP stopped the development for it and it has been deprecated since the release of PHP5. It’s removed now in PHP7. This extensions requires a lot of coding to prevent MySQL injection attacks from happening.

There is no reason of course to stop using it more than that it’s deprecated in the newer versions of PHP. Any code using it should be considered legacy code and must be updated.

MySQLi

In addition to having the ability to be used in and object-oriented code or in a procedural one, MySQLi supports transactions, prepared statements and it has more debugging capabilities than MySQL extension. It was the best choice until PDO came out.

As we said, MySQLi supports both Object-Oriented approach as well as Procedural approach. To use the object-oriented way, follow this example:

<?php
$servername = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

// Create connection
$conn = new mysqli($servername, $username, $password,$database);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";
?>

And the following code does the same job but using the procedural way:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$database = "db_name";

// Create connection
$conn = mysqli_connect($servername, $username, $password,$database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";
?>

PDO

First it supports prepared statement so the risk of SQL injection attacks is minimized. It also supports stored procedures, named parameters and it has the best performance benchmarks over all the PHP MySQL extensions.

One other big advantage is that PDO not only supports MySQL, but it also supports the following database drivers:

• PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
• PDO_FIREBIRD ( Firebird/Interbase 6 )
• PDO_IBM ( IBM DB2 )
• PDO_INFORMIX ( IBM Informix Dynamic Server )
• PDO_MYSQL ( MySQL 3.x/4.x/5.x )
• PDO_OCI ( Oracle Call Interface )
• PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
• PDO_PGSQL ( PostgreSQL )
• PDO_SQLITE ( SQLite 3 and SQLite 2 )
• PDO_4D ( 4D )

Please note that you need to have the database driver installed on your server in order to use its driver.

Conclusion

You can use each of MySQLi or PDO but if you want to write code that will be long maintained an available to be extended to support more database drivers. But if you still use MySQL extension, please update your code.