Learn MySQL with the Curriculum Team

Which PHP Extension Should I use for MySQL Development?

Mark Lewin
MySQL Curriculum Developer

For many programming languages, it's pretty easy to figure out which Connector to use. Java has Connector/J, if you're using C# or ASP you'll opt for Connector/.NET: the clue is in the name.

For PHP however, things aren't quite so straightforward. If you want to write a PHP application that communicates with a MySQL database, you have a choice of PHP extensions that you can use: mysql, mysqli, and PDO_MySQL.

We can simplify that list right away by discounting the mysql extension. This is old and was intended for use with MySQL versions before 4.1. It does not support many of the things that you can take for granted with the other two, such as improved MySQL authentication protocols or the ability to create prepared statements to prevent against SQL injection. It was dropped from PHP 7. In fact, you would have to jump through a couple of hoops just to get it to work with a later version of MySQL. So don't bother!

The mysql extension was replaced by mysqli (mysql "improved"). This extension is intended for use with PHP 5.0.0 and MySQL 4.1.1 and later. It fully supports the authentication protocol used in MySQL 5.0, as well as the Prepared Statements and Multiple Statements APIs. In addition, this extension supports object-oriented programming techniques, whereas the older mysql extension was strictly procedural.

PDO_MySQL is the most recent extension, where PDO stands for "PHP Data Objects". Unlike mysqli which is specific to MySQL, PDO is a general database abstraction layer with support for MySQL. As such, it only implements features that MySQL shares with other databases. As PDO and MySQL have matured, the functionality gap between PDO_MySQL and mysqli has narrowed and, if you do find a MySQL feature that is unsupported, it is likely to be pretty arcane. PDO_MySQL is a great choice if you want to write code that is easy to port to other database platforms.

Both mysqli and PDO_MySQL are PHP extensions that are lightweight wrappers on top of a C client library, either the mysqlnd library or the libmysqlclient library. The mysqlnd library has been part of the PHP distribution since 5.3.0. It offers features such as lazy connections and query caching that are not available with libmysqlclient, so Oracle recommends sticking with mysqlnd unless you have a compelling reason not to. If you wanted to switch to libmysqlclient, you would have to recompile the extension.

For a more in-depth comparison of the PHP extensions for MySQL, visit the PHP website.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha