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 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.
- 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 (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.
|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|
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: