Migrate from the MySQL Extension to PDO

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

Win an Annual Membership to Learnable,

SitePoint's Learning Platform

  • http://port9.com Joshua Oiknine

    So PDO is like ODBC connections on Windows with DSNs and connection strings. The one question I have is how PDO performs over mysqli.

    Like the new site and like the article.

    • http://zaemis.blogspot.com Timothy Boronczyk

      Thanks for the encouragement! To answer your question, PDO is only *slightly* slower than the MySQLi extension. Jonathan Robson did some benchmarks a while back and shared them on his blog (http://jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks).

  • http://www.chrisupjohn.me Chris

    Thanks for the overview, i have seen PDO becoming popular but having used MySQL and MySQLi for so long i didn’t see see a reason to learn PDO. Thanks to this tutorial i think i might start investing some time in it.

  • http://blog.leonardchallis.com/ Leonard

    Can you explain the thinking behind how you bind the parameters to uninitialised variables then set them late? I presume it’s using (some form of) reference variable. Does binding initiate those variables? Or could some PHP configs cause warnings?

    • http://zaemis.blogspot.com Timothy Boronczyk

      You are correct. As stated in the documentation (http://www.php.net/pdostatement.bindparam.php), bindParam()’s second argument is a variable reference. The advantage of this over another method, bindValue(), is that the variable is evaluated only when execute() is called. You can assign different values to the bound variable, eg. in a loop, and execute() will always use the current value in the query it sends.

  • grizzley

    Having spent the last 2 years learning from Kevin Yanks book ‘Build your own database driven website using php and mysql’ and developing my own site I feel I am just about ready to progress to the next step of OOP , PDO etc and articles such as this are much appreciated, as I think the phpmaster site will be.

  • http://www.handcraftedwebsites.co.uk Cottage Stuff

    Congrats on the new site, and thanks for putting this PDO tutorial together. Very helpful. Let me just highlight something: This tutorial only really makes sense to someone who already is up to speed with OOP PHP stuff. I imagine that a lot of us who are not yet up to speed would be over the moon to see a nice series of carefully planned tuts to help us get out of our bad procedural ways and start doing slick stuff with classes, methods and the operators that Larry Ulman never told us about.

    • http://zaemis.blogspot.com Timothy Boronczyk

      Stay tuned… there is a beginner-focused OOP tutorial in the works

      • http://www.alabiansolutions.com Alabi

        Is the OOP PHP here already? Please I will like to know when it comes into existence. Why not add a plugin to phpmaster that alert one via mail if there is an update to ones comment.

  • http://www.frannleach.com frann

    If i can’t just look up even something as simple as how to connect to the database, in my view pdo is not yet ready for use

  • Leszek

    Prepared statement too briefly. There is no info about: setlocale(LC_ALL, ‘pl_PL’); for proper handling int,
    Bind type and length: $query->bindParam(“:lname”, $lastName,PDO::PARAM_STR, 12);

  • Jeff

    I’ve got a question I’ve been mulling over for a little while and maybe you would have some insight.

    My app is using the old mysql method, and I’m looking at the pro’s and con’s of mysqli and pdo. I kinda like PDO more at this point, so here’s the question.

    While I’m still updating the bajillions of mysql references, is there a way to convert a PDO->query() object to a mysql reference so my code can still be functional on both while I track down every little query?

    Currently I have all my mysql statements running through a ‘debug’ class, everything gets passed to a method that actually runs the query (and dumps some info about what’s going if some debug variables are present).

    Essentially it’d be nice if I could take the object the PDO query returns and run mysql_fetch_assoc() and mysql_num_rows() and other mysql based functions against it. If that makes sense. I’ve been unsuccessful finding anything like this so far.

  • Brandon

    I’ve yet to hear a valid reason to use PDO over MySQLi. From everything I’ve researched on the topic, what I gather is, if you’re using a MySQL database, there is no reason you should be using PDO.

  • ktc

    This simplified tutorial eased my fears about weaning myself off the MySQL extension, whether migrating to PDO or MySQLi. It made converting my home-grown CMS much easier than I thought it would be. thanks

  • http://easymemberpro.com Michael D Price

    I think that PDO might be the best choice if building NEW applications, but seems mysqli is the right choice for upgrading an application as mysqli is much closer to the old mysql, and you can virtually switch them over with very little modification to the current code.
    Im currently using a custom database class for my queries(using mysql), so All the code can be updated in that one file ;)

  • Marty

    hey timothy,
    thanks for the valuable info relating to PDO man….I was looking out for the info. you have really explained it a real nice way. Expecting for PHP related topics from you.
    Thanks

  • http://ba.rrypark.in Barry P

    To anyone using mysql_* functions: please stop. The use of these functions is discouraged as they are to become deprecated. Switching to mysqli_* functions is easy. PDO is better (doesn’t fix you or your code to a specific database for future changes/upgrades).

  • http://www.blinks.org.uk John

    Great article. When building something new it wouldn’t occur to me to use the mysql_ functions – it makes sense to use either frameworks or PDO (especially prepared statements) as you get the security of using code that isn’t going to be deprecated and with better escaping to help prevent SQL injection attacks.

    I’m guilty though of not updating old code, the article has helped remind me that I should do, and that it doesn’t have to be too hard.

    Cheers

  • http://therobbrennan.com Rob

    What a great overview. I had no idea that the familiar mysql* functions were being deprecated, and this is such an easy replacement. Thanks.

  • http://oggosoft.com didoex

    Wonderful …. it’s Very Important Essay for the bignner of Pdo

  • http://www.floristsnear.co.uk Chrysanthemums

    Having scoured the internet after seeing a nettuts article about how we shouldn’t be using mysql anymore and PDO instead I initially got confused on how the code worked with existing code, say if i’m outputting a set of results into an existing template, I couldn’t find a single article showing this using PDO, except this one! I was very pleased to see I didn’t need to change a thing with relation to my existing templates and echoing $row,column name still works the same as before.. Now to learn some of the new features :p