Entries filed under SQL

PDO and MySQL code Migration Guide

Posted on August 11, 2014 Comments

PDO, or PHP Data Objects have been implemented since PHP 5.1 and are the preferred way of accessing MySQL database information over the traditional mysql_* function library. With PDO, you get prepared statements, parametrized queries, and it’s object oriented.  Even though the mysql_* family of functions have been deprecated, their use remains popular at the time of writing simply because of how long they’ve been around. As someone who tries to stay up to date, I have been pushing myself to use PDO more often for new projects and trying to migrate old ones. PDO can be a little daunting if you have been using mysql_* for years; but this quick guide shows some common mysql_* methods of extracting data and the PDO alternative side by side.

I think seeing equivalent things side by side is useful. You may have an application that is depending on getting the same result you’d expect from the mysql_* functions, but not know how to go about implementing it. This guide will help you with migrating some of your old code as well as teach you new methods. It’s really not so scary or complicated at all. It’s important to learn the basics now because mysql_* will eventually be dropped.

Connecting to a MySQL database:

This is relatively simple to follow and it’s actually much shorter than the mysql_* functions.

mysql_*:

PDO:

Error Modes and Handling:

With mysql_query(); if there is a syntax error, the result is silent and you have to figure it out with mysql_error().   The equivalent to mysql_error(); is $DB->errorInfo();  By default, PDO’s errors are the same way, they are silent. This might be preferable when you’re doing procedural programming, but if you’re looking for something more object-oriented look into PDO error modes.

To change PDO error modes, use setAttribute after your database connection. The below code will activate exceptions in PDO:

To use the exception to handle errors gracefully, here’s how:

Selecting Row Data:

Getting right down to it – selecting an associative array from a database table is one of the single most common operations.

mysql_*:

PDO:

The PDO code is much cleaner and easier to remember. If you know the foreach loop already, which you should, you’re golden. Just place the query directly in the foreach loop.

Alternatively, you can also select rows without a loop. This is useful for selecting a single row where only one result is expected. The example below selects a unique primary key called “member_id.”

mysql_*:

PDO:

There is not much of a difference in result here, but it shows that it can be done.

Row Counting:

I personally believe that using SELECT COUNT() on a single column is best practice for finding the number of rows in a result set. However, you can still get the number of rows in the result via PHP, and I’ve done this myself many times out of convenience.

mysql_*:

PDO:

Note that again, I don’t endorse this as the best method – SELECT COUNT(id) would be far better in most instances and more efficient since the result is taken directly from the DB engine.

DELETE, INSERT, UPDATE rows:

Up until this point we’ve been using PDO::query as shown in many of the examples above. But now we’re about to use PDO::exec. This is the preferred way of executing any query that performs an action where we might expect changes in rows. Even though deleting, inserting, and updating will work without fail using $DB->query(), you’ll want to use $DB->exec() instead since it returns information on the number of rows that were changed, if any. I’m just going to show you a deletion example.

mysql_*

PDO:

It is literally that simple in PDO.

Prepared Statements/SQL Injection Prevention:

One of the top reasons to switch to PDO is explained here: prepared statements. Prepared statements ensure queries are formed correctly and get executed safely with less hassle than using the mysql functions.

Let’s take a look at SQL code that accepts input:

mysql_*

This may look comfortable to you if you’ve been used to doing this for a long time… but it is a mess. It’s a disaster to read as well.

PDO:

Do you see what happens here? We use PDO::Prepare() to create an SQL query template and fill in the input values with placeholders. PDO::execute parses the query and runs it. No fuss, no hassles with writing out filtering functions a million times. Everything is simply taken care of.  I used an array to define the placeholder values with input, but there are many more methods you can use to accomplish this. I personally think this one is the easiest to deal with when trying to switch from mysql_* functions to PDO, but to each their own. You can read about more prepared statements in the manual of course.