In order for PHP to communicate well with database provider which is MySQL, we need to decide on which PHP “driver” that we should use. The term “driver” is not so correct but its fair enough to describe the MySQL connecter from PHP point-of-view.

mysql extension

  • Mysql extension is the original extension designed to allow you to develop PHP applications that interact with a MySQL database.
  • The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3.
  • This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.
  • Develop using PHP extension framework.
  • Mysql extension does not support Charsets, Prepared Statements and Stored Procedures.

mysqli extension

  • MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.
  • Mysqli was faster perform faster queries result than PDO-mysql and mysql extension.
  • Developed using PHP extension framework.
  • Mysqli does support Charsets, Prepared Statements and Stored Procedures.
  • Server-side prepared statements which can increases the number of round-trips or memory usage.
  • If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

PDO-mysql extension

  • PDO (PHP Data Objects) offer great security than other without much hassle, but for transition i would suggest you to move to mysqli since it faster, easier than PDO, and most api/syntax are quite same with the old mysql extension.
  • PDO allows you to use the same API calls for various databases. You can scale your application to use other databases with just a few code changes.
  • The PDO library has much of the security built in.
  • PDO will need to have different queries to return number of rows.
  • Cannot use /* */ commenting.
  • Server and client-side prepared statements.
  • Complete database abstraction layer.

Comparison

Features MySQL Improved Extension – ext/mysqli PDO Driver for MySQL – PDO_MYSQLND Classical MySQL Extension – ext/mysql
PHP version introduced 5.0 5.0 Before 3.0
Comes with PHP 5.x yes yes Yes
Comes with PHP 6.0 yes yes Yes
MySQL activities active development active development as of PHP 5.3 maintenance only
MySQL recommended for new projects Yes Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statement Yes Yes No
API supports client-side Prepared Statement No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statement Yes Most No
Supports all of MySQL 4.1+ Yes Most No

Notes

The mysql extension, the mysqli extension and the PDO-mysql driver can each be individually configured to use either libmysql or mysqlnd. As mysqlnd is designed specifically to be utilised in the PHP system it has numerous memory and speed enhancements over libmysql.

So, when writing an application that is 100% guaranteed to always use MySQL, using mysql or mysqli extension will work better than PDO. But when we need the flexibility of a database abstraction layer, PDO will make your code much more solid and portable.

PDO offer great security than other without much hassle, it is highly recommened to use mysqli since it faster and easier than PDO, and most syntax are similar with the old mysql extension.

For list of supported connector in MySQL, you can refer this page:
http://www.mysql.com/products/connector/

One Response to PHP Driver: MySQL vs MySQLi vs PDO MySQL

  1. AzharNo Gravatar says:

    Really! Thankyou for the article :) This help me to understanding the difference both of them.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>