What is the difference between MySQL, MySQLi and PDO?

down vote

There are (more than) three popular ways to use MySQL from PHP. (DEPRECATED) The mysql functions are procedural and use manual escaping. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned. PDO_MySQL, is the MySQL for PDO. PDO has been introduced in PHP, and the project aims to make a common API for all the databases access, so in theory you should be able to migrate between RDMS without changing any code (if you don't use specific RDBM function in your queries), also object-oriented. Connection : // PDO 1 $pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password'); // mysqli, procedural style 1 $mysqli = mysqli_connect('localhost.com','username','password','data'); // mysqli, object oriented style 1 $mysqli = new mysqli('localhost.com','username','password','data'); API Support : Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like. Database Support : The main advantage of PDO over MySQLi is its database driver support. PDO supports 12 different drivers and MySQLi supports MySQL only. To get the list of all drivers that PDO supports, use following code: 1 var_dump(PDO::getAvailableDrivers()); I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary. PDO Drivers ¶ Table of Contents ¶ CUBRID (PDO) MS SQL Server (PDO) Firebird (PDO) IBM (PDO) Informix (PDO) MySQL (PDO) MS SQL Server (PDO) Oracle (PDO) ODBC and DB2 (PDO) PostgreSQL (PDO) SQLite (PDO) 4D (PDO)