129

What is the difference between MySQL, MySQLi and PDO?

Which one is the best suited to use with PHP-MySQL?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Chintan Parekh
  • 1,291
  • 2
  • 9
  • 4

4 Answers4

102

There are (more than) three popular ways to use MySQL from PHP. This outlines some features/differences PHP: Choosing an API:

  1. (DEPRECATED) 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.

AbraCadaver
  • 78,200
  • 7
  • 66
  • 87
Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • 9
    There is often the confusion that for procedural you have to use mysql_*() functions where for OO you use mysqli! The fact is that mysqli completely replace the early (PHP 4) implementation of mysql. It is possible to use both the procedural style and the OO one with mysqli. – Patrick Allaert Jun 19 '10 at 09:31
47

Those are different APIs 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, 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.

So it depends on what kind of code you 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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
RageZ
  • 26,800
  • 12
  • 67
  • 76
3

Specifically, the MySQLi extension provides the following extremely useful benefits over the old MySQL extension..

OOP Interface (in addition to procedural) Prepared Statement Support Transaction + Stored Procedure Support Nicer Syntax Speed Improvements Enhanced Debugging

PDO Extension

PHP Data Objects extension is a Database Abstraction Layer. Specifically, this is not a MySQL interface, as it provides drivers for many database engines (of course including MYSQL).

PDO aims to provide a consistent API that means when a database engine is changed, the code changes to reflect this should be minimal. When using PDO, your code will normally "just work" across many database engines, simply by changing the driver you're using.

In addition to being cross-database compatible, PDO also supports prepared statements, stored procedures and more, whilst using the MySQL Driver.

3

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.

Sarfraz
  • 377,238
  • 77
  • 533
  • 578