Geekery, Programming

Perhaps it is because of the recent slew of high profile SQL injection attacks, or perhaps because it was just finally time to bite the bullet and make the switch. PHP internal emails have revealed that there is finally an official movement to deprecate the ext/mysql and mysql_* family of functions from PHP.

For me, this couldn’t have come any sooner. Although the community has been advocating a change for a long time, much of the documentation and tutorials that exist on the Internet still point to using mysql_* functions instead of their more current alternatives. Many times throughout the day, nearly every day, new PHP developers ask questions in the ##php IRC chat on irc.freenode.net and are delivered a swift !+mysql is old indicating that:

mysql_* functions are no longer recommended for new development. For new projects use mysqli (php.net/mysqli) or PDO (php.net/PDO). The MySQL developers developed guidelines and a script for migrating your code http://forge.mysql.com/wiki/Converting_to_MySQLi. Most people here will recommend PDO: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

You can find the full email if you want to see the text in it’s entirety but the main points for suggesting things move along are:

  • Softly deprecate ext/mysql with education (docs) starting today
  • Not adding E_DEPRECATED errors in 5.4, but revisit for 5.5/6.0
  • Add pdo_mysql examples within the ext/mysql docs that mimic the current examples, but occasionally introduce features like prepared statements
  • Focus energy on cleaning up the pdo_mysql and mysqli documentation
  • Create a general “The MySQL situation” document that explains the situation

Which should you choose? Should you use PDO or mysqli?

The personal preference of this author is definitely PDO. The possibility of interoperability with other databases is nice, but ability to use exceptions to handle issues with the database is really the selling point for me. Exception handling and other features means that PDO is recommended more often than mysqli_* functions for new development. This is reflected in responses to the mailing list.

To educate yourself on the issue, you can check out additional information about the differences between the options in the PHP manual. For a quick overview, the table near the bottom of the page in the manual is a nice, quick reference:

  PHP's mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP's MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No

However, my opinion and the opinion of other PHP developers on the matter, may not make a huge difference when it comes to future development. Johannes Schlüter, an engineer on the MySQL/Oracle team, responded to the PHP mailing list with some interesting information about which direction their team is headed:

I’m not sure the current PDO is “the” alternative. We (= MySQL/ORACLE) focus mostly on mysqli, that’s the extension providing access to all current and future features of MySQL. True, many features could be added to PDO but there are two design decision in PDO which make this bad:

  • The parser used for identifying statement place holders is very basic, as it is implemented in PDO core, not the drivers, which leads to FRs like #54929 or the famous LIKE issue.
  • driver-specific functions are implemented by using __call() which means there is no good introspection mechanism to check whether a feature is available or not in the current setup.

Besides these two items there are every now and then reports on PDO_mysql which in fact are caused by limitations in the PDO design which can’t be bypassed by the driver implementation.

A good abstraction layer would certainly be good for the language but for now we (=MySQL/ORACLE) consider mysqli the preference.

It sounds like we will have to wait and see which will be the accepted alternative for future development (if any is ever considered accepted). A huge tipping point would be adding exception handling in the mysqli_* family of functions but that doesn’t appear to be an important development direction at the moment. For now, I will continue to happily use PDO and await the day for users to start wondering why most of the MySQL/PHP tutorials on the web are throwing E_DEPRECATED warnings.

For more information about converting your code to use the new libraries or to learn about them, consider the following links:

It’s about time.

Leave a Reply

  • (will not be published)