Migrate from the MySQL Extension to PDO

Key Takeaways

• Migrating from the older MySQL extension to the newer PDO (PHP Data Objects) extension is recommended due to its consistent API that works with a variety of databases, preventing the need for PHP code modification if another database is used in the future.
• PDO, compared to MySQL and MySQLi, offers an interface that consolidates most of the functionality previously spread across major database access extensions, and exposes high-level objects for working with database connections, queries, and result sets.
• PDO is usually part of the default PHP installation as of version 5.1.0 and includes a low-level database-specific driver. It allows for easy connection to a MySQL database server, issuing queries, and receiving results.
• PDO supports prepared statements and different error handling paradigms, making it more secure and flexible. It automatically quotes and escapes any bound parameters, which can help guard against SQL injection vulnerabilities.

This guide is a practical overview to help you begin migrating your existing PHP code from using the older MySQL extension to the newer PDO database abstraction extension. I won’t go into all of the features available to you with PDO, but I will share with you the basic methods you will need to know and offer a few tips so your migration will be as quick and painless as possible. First, you should understand where PDO sits in the PHP ecosystem. There are actually three ways to interface with a MySQL database from PHP: the first is with the MySQL extension, the second is with the MySQLi extension and the third is with PDO. The MySQL extension is the oldest of the three and was the original option developers used to communicate with MySQL. The extension is now being deprecated in favor of the other two alternatives because of improvements made in newer releases of both PHP and MySQL. MySQLi is the “improved” extension for working with MySQL databases. It takes advantage of features that are available in newer versions of the MySQL server, exposes both a function-oriented and an object-oriented interface to the developer and a does few other nifty things. If PDO isn’t right for your project, then this is the extension you should be using. Then most recent extension is PDO, which stands for “PHP Data Objects.” PDO offers an API that consolidates most of the functionality that was previously spread across the major database access extensions, i.e. MySQL, PostgreSQL, SQLite, MSSQL, etc. The interface exposes high-level objects for the programmer to work with database connections, queries and result sets, and low-level drivers perform communication and resource handling with the database server. A lot of discussion and work is going into PDO and it’s considered the appropriate method of working with databases in modern, professional code. When possible, I recommend people skip the middle man so to speak and migrate from the MySQL extension to PDO rather than to MySQLi. PDO offers a consistent API to work with a variety of databases, so you won’t have to modify your PHP code if you ever have to use another database. Also, while the MySQLi extension is currently maintained, there’s always the chance it may be deprecated in the future. Choosing to migrate to PDO now means you won’t have to migrate to it later and update your code twice. I use PDO and you should too!

Installing and Configuring PDO

Once you’ve decided you want to modernize your code, you’ll need to make sure PDO is properly set up and configured. As I mentioned before, PDO consists of two parts: the extension itself which exposes the interface and a low-level database-specific driver. Drivers exist for a growing list of databases, including MySQL. PDO and the SQLite-specific driver should be part of the default PHP installation as of version 5.1.0 and the MySQL-specific driver would still need to be installed, but most Linux distributions often have different ideas how PHP should be compiled and packaged. In fact, many of the major distros now package the MySQL extension, the MySQLi extension and the MySQL PDO driver together in the same package. Chances are if you’re using MySQL on such a system then PDO is probably already set up for you.

• Ubuntu builds PHP with PDO (but does not package the SQLite driver by default) and bundles together the MySQL extensions and driver. You can install the extensions and driver by running sudo apt-get install php5-mysql.
• Fedora also bundles the extensions and driver together, but packages PDO as a loadable module. Running sudo yum install php-mysql installs the MySQL extensions and driver and the php-pdo package will automatically be included as a dependency.
• SUSE is the only distribution of the three that builds PHP the way the PHP Team recommends with regard to PDO; they builds PHP with PDO and include the SQLite driver. yast2 --install php5-mysql will install the MySQL extensions and driver you need.

On Windows, all of the PDO drivers are included in the ext folder that was created when you installed PHP from the pre-compiled binary’s archive. You only need to update your php.ini by adding or uncommenting the line:

extension=php_pdo_mysql.dll

It’s always possible to compile the drivers yourself in a worse-case scenario. Make sure the MySQL libraries and other development files are installed and accessible in your build environment since most problems that people encounter while trying to compile something results when the compiler can’t find various header files or libraries to link against.

Basic Querying

The basic workflow for using the MySQL extension’s functions to work with a database can be thought of as a 5-step process:

1. Establish a connection to the database server and select the database you’ll be working with
2. Construct a query to send the server
3. Send the query
4. Iterate over the returned result rows
5. Free the resources used by the result and possibly the database connection

<?php
// Step 1: Establish a connection
$db = mysql_connect("localhost", "testusr", "secretpass");
mysql_select_db("testdb", $db);

// Step 2: Construct a query
$query = "SELECT * FROM foo WHERE bar = '" . mysql_real_escape_string($zip) . "'";

// Step 3: Send the query
$result = mysql_query($query, $db);

// Step 4: Iterate over the results
while($row = myql_fetch_assoc($result)) {
    print_r($row);
}

// Step 5: Free used resources
mysql_free_result($result);
mysql_close($db);

With PDO, the same process can be followed and looks like this:

<?php
// Step 1: Establish a connection
$db = new PDO("mysql:host=localhost;dbname=testdb", "testusr", "secretpass");

// Step 2: Construct a query
$query = "SELECT * FROM foo WHERE bar = " . $db->quote($zip);

// Step 3: Send the query
$result = $db->query($query);

// Step 4: Iterate over the results
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

// Step 5: Free used resources
$result->closeCursor();
$db = null;

The process of connecting to the server and selecting the database with PDO is performed using a DSN instead of the myql_connect() and mysql_select_db(). A DSN is an abbreviation for “Data Source Name” which is a string that identifies which PDO driver to use to communicate with the database and conveys any essential connection information such as the host server, connection port and database name. Depending on your database, the format of the DSN you provide may vary. The major components necessary for each driver are documented on php.net, but unfortunately they are sometimes hard to find. I recommend using the site’s search feature to look for the term “DSN” in the online documentation. Also notice that PDO::quote() both escapes and quotes a value whereas mysql_real_escape() only escapes the value. You should keep this in mind so you don’t end up with extra quotation marks in the final query and scratch your head wondering where they came from. While sometimes you can get away without freeing the result resource with mysql_free_result(), I recommend you don’t get into such lazy habits when using PDO. If you still have a result set from a SELECT query which hasn’t called closeCursor() yet and you try to do an INSERT or UPDATE on the table, the change may silently fail and again you’ll be left scratching your head. It’s better to make a habit out of doing things properly to avoid frustrating situations later.

Digging (Slightly) Deeper

What has been covered so far is enough to get you started in migrating your PHP app from MySQL to PDO as quickly and painlessly as possible, but there’s more to PDO than what I’ve shown you. I’d like to also highlight a couple of other features PDO you may find useful: prepared statements and error handling.

Prepared Statements

A prepared statement is a representation of a query as an object. The benefit of representing a query as an object is that additional logic can be associated with it that would otherwise be difficult to manage for a flat string representation. For example, you can specify placeholders in a query which are then bound to variables in your script. This is helpful if you’re running the same query multiple times to satisfy the request but with different parameters. The query only needs to be prepared once, but can be run many times with different values as the value of the variables change.

<?php
// collection of user information to import into the database
$users = ...

// specify the query "template"
$query = $db->prepare("INSERT INTO users (first_name, last_name, email) VALUES (:fname, :lname, :email)");

// bind the placeholder names to specific script variables
$query->bindParam(":fname", $firstName);
$query->bindParam(":lname", $lastName);
$query->bindParam(":email", $email);

// assign values to the specific variables and execute the query
foreach ($users as $u) {
    $firstName = $u["first_name"];
    $lastName = $u["last_name"];
    $email = $u["email"];
    $query->execute();
}

The parameters don’t have to be quoted or escaped with prepared statements because the underlying driver automatically quotes and escapes any bound parameters for you. Because of this, many people like to use prepared statements to guard against SQL injection vulnerabilities even if the query is only executed once.

Error Handling

You can specify one of three different error handling paradigms with PDO by using PDO::setAttribute() to set the error handling mode.

<?php
$db = new PDO($dsn, $user, $passwd);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The modes are:

• Silent Mode (PDO::ERRMODE_SILENT) – sets the internal error code but does not interrupt the script’s execution (this is the default setting)
• Warning Mode (PDO::ERRMODE_WARNING) – sets the error code and triggers an E_WARNING message
• Exception Mode (PDO::ERRMODE_EXCEPTION) – sets the error code and throws a PDOException object

Regardless of which mode is set, there is an internal error code which is set and you can check using the errorCode() and errorInfo() methods of the PDO and PDOStatement objects. These are analogous to mysql_errno() and mysql_error(). errorCode() returns a 5-character string as defined in the ANSI SQL-92. errorInfo() is generally more useful as it returns an array that includes an error message in addition to the 5-character code.

<?php
$query = "SELECT * FROM foo WHERE MALFORMED QUERY";
$result = $db->query($query);
$error = $db->errorInfo();
if (!is_null($error[2])) {
    echo "Query failed! " . $error[2];
}
...

If you are using the exception mode, a description of what went wrong is available through the exception object’s getMessage() method.

<?php
$query = "SELECT * FROM foo WHERE MALFORMED QUERY";
try {
    $result = $db->query($query);
    ...
}
catch (PDOException $e) {
    echo $e->getMessage();
}

Summary

Now you should have the basic knowledge necessary to start migrating from the older MySQL extension to the newer PDO extension. You’ve seen that while most of the time PDO is already available, it’s easy to set up if for some reason it isn’t. You’ve also seen how easy it is to connect to a MySQL database server, issue queries and receive the results. Of course this was just a practical overview; to learn more about what PDO has to offer and how you can take advantage of it, I encourage you to read the available documentation at php.net. Image via Dirk Ercken / Shutterstock

Frequently Asked Questions on Migrating from MySQL Extension to PDO