What is difference between mysql,mysqli and pdo ?

Question

What is difference between mysql,mysqli and pdo ?
Which one is the best suited to use with PHP-MYSQL?

Answer 1

Those are differents API to access a MySQL backend

• the mysql is the historical API
• the mysqli is a new version of the historical API, it should perform better and have a better set of function, also the API is object oriented
• PDO_MySQL, is the MySQL for PDO, PDO has been introduced in PHP, 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

So it depend what kind of code your want to produce, if you prefer object oriented layers or plain functions...

My advice would be

1. PDO
2. mysqli
3. mysql

Also my feeling, the mysql API would probably being deleted in future releases of PHP

Answer 2

mysqli is the enhanced version of mysql.

PDO extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

Answer 3

There are (more than) three popular ways to use MySQL from PHP.

1. The mysql functions are procedural and use manual escaping.
2. mysqli is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements.
3. 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.

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.

Answer 4

There is a table comparing the 3 API features. Use Mysqli whenever possible as is the latest launched after PDO and is and will be better maintained in the future.