PHP5: MySQL

PHP5 has added some new interfaces for MySQL.

MySQLi

The old MySQL extension is not really intended for MySQL versions above 4.1.3. Instead, you should use the MySQLi extension (“i” for “improved”) that comes with PHP5. It includes support for the new functionality in MySQL 4.1 and above:

• prepared statements
• multiple statements
• transactions
• enhanced debugging support
• embedded server support

MySQLi is an object-oriented interface, but also provides a procedural interface similar to the old extension. For example, you can use the query() method of the MySQLi class, or you can use the procedural mysqli_query(). Check the manual for the full list of functions. It is very similar to the old extension (even the OOP methods), so you should be able to pick it up quickly.

PDO

PHP Data Objects, or PDO, is an abstraction layer. That means you can use PDO for any supported database, with only minor changes to the code. This is like the database layers in Java (JDBC) or Perl (DBI).

Strictly speaking, PDO is not a database abstraction layer; it is a data-access abstraction layer. That is, it won’t rewrite the SQL or emulate missing features. There are higher-level database abstraction libraries out there that can be used for that.

You can find lots of PDO tutorials on the web, and of course the PHP manual is pretty good. It’s not much more complex than the MySQLi interface, even if you (wisely) use prepared statements:

try
{
  $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
  $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
  if ($stmt->execute(array($_GET['name'])))
  {
    while ($row = $stmt->fetch())
    {
      print_r($row);
    }
  }
  $dbh = null; // destroy the object == closing the connection
}
catch (PDOException $e)
{
  print "Error!: " . $e->getMessage() . "<br/>";
  die();
}

Which To Use?

According to the PHP manual, MySQLi is the preferred interface. However, both MySQLi and PDO are in active development, so both are suitable for new PHP code. (The old MySQL interface should not be used.)

PDO is more abstract, so it is probably the best choice if the underlying database could change. It doesn’t have full support for multiple statements and all the latest features of MySQL, but should cover most of the functionality you’d need. PDO also uses a different MySQL driver that is newer and faster than the MySQLi driver, and it works with any version of MySQL.

If you need an interface closer to the database layer, then MySQLi is the way to go. But, it doesn’t have prepared statements. (It does support server-side prepared statements.)

The tricky part will not be learning the syntax of the new extensions, but learning and using the new database features: prepared statements, transactions, stored procedures, and all that good stuff.