Update mysql_query to PDO

Goal: To explain the differences between the old PHP MySQL extension and its mysql_query function, and the new PDO (PHP Data Objects) functions.

Prerequisites: This guide is written under the assumption that you have a pretty good grasp on how programming works in general, such is if's, loops, and function calls. Having used the PHP mysql_* functions would be a big help since the purpose of this guide is to compare that to the PDO functionality.

Why do I care?

For a long time the mysql_* functions have been around with the mysql extension in PHP. They were a fairly simple way of accessing your MySQL databases, and it just worked. But now PHP has deprecated this library as of PHP 5.5, meaning that if you have deprecated warnings turned on in your PHP.ini file you will start to see deprecation messages show up on your site. This also means, that PHP is not going to support these functions in future versions and will likely remove the functionality completely. This means that you need to use an alternative option, mainly PDO_MYSQL (which we'll covere here in a bit) or you can use the newer mysqli extension.

Why is this happening?

Well, to get the full scoop on things you can hop over to PHP's website and view their wiki article on all the reasons why. Really it comes down to the following reasons:

• Database security with the mysql extensions left a lot to be desired. The old mysql_* functions did not support prepared statements, meaning that things like MySQL injection really had to be handled by something else.
• The old mysql extension did not support access to all the features in the newer versions of MySQL. Such as the ones listed below:
  • Stored procedures (can't handle multiple result sets)
  • Prepared Statements
  • Encryption (SSL)
  • Compression
  • Full Charset support
• The old MySQL extension is hard to maintain code. It is not getting new features. Keeping it up to date for working with new versions of libmysql or mysqlnd versions is work, and the PHP team thinks that their time could be better spent improving other things.

Keep in mind that the mysql extension is ancient, it has been around for more than 15 years (since PHP 2.0) and so there are many concerns that such a large code base out in the wild is dependent on the older functions. But like all good things when it comes to technology, everything has an expiration date and ext/mysql's is coming up.

What about applications that use this older functionality?

Well, there is good news and bad news here. Many times when you're bolting on to an application such as wordpress or concrete5, you might be inclined to completely ignore their database abstraction layer altogether. This means that you will have the very unfortunate time of going through all your code and replacing old functions with new ones. However, if you did things properly, usually when programming inside of another application there are function calls exposed which will run your queries through their own database handler, which in turn would call the mysql or mysqli or pdo functions to get the results back. If you used these, then all you have to do is sit back and wait for the core to be updated.

Now, if you're using some ancient system that isn't supported anymore, then it may be time to think about getting it updated or to start looking for alternate solutions. In the meantime, you can continue running on older versions of PHP, and nothing is going to break, but from experience applications that go 15 years without updates become cryptic mysteries to programmers later on after the documentation has been pulled or there has been complete language overhauls done. Best to get ahead of the game on this problem.

I'm a PHP developer. What should I be doing differently?

As mentioned above the replacements for the mysql_* functions are two newer extensions, mysqli (mysql improved) and PDO_MYSQL (PHP Data Objects for MySQL). Below we'll show you some syntax differences and functionality differences between the old MySQL extension, and the new PDO extension. We normally choose to use PDO because of its OOP style, over MySQLi, but MySQLi is a safe choice and should also be supported for many years to come.

How to Connect to MySQL using PDO

Now down to the meat and potatoes of the guide for programmers. Here I'll show you how you would have originally connected to a MySQL database using the old ext/mysql and an example of how to connect using PDO as well. Note: All code snippits below this connection one also require a connection to be present in order to run so keep this code handy.

<?php

$host = 'localhost';
$database = 'employees';
$username = 'root';
$password = 'P@ssW0rd';

/**
 * START OF CONNECTION CREATION AND DATABASE SELECTION
 */

//connect to database using mysql_* (the old deprecated way)
$link = mysql_connect($host, $username, $password);
if(!$link) {
    die('Error: ' . mysql_error());
}

$dbSelected = mysql_select_db($database, $link);
if(!$dbSelected) {
    die('Error: ' . mysql_error());
}

//connect to database using PDO (the new way)
try {
    $dbh = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
    print 'Error: ' . $e->getMessage() . '<br />';
    die();
}

You can see pretty quickly that PDO uses the object oriented style, and exposes functions on the object instead of setting variables and troubleshooting them individually. It also has its own exceptions so that they are easy to detect when you build your application instead of a bunch of if blocks looking for different problems on different variable assignments.

How to execute a simple MySQL query using PDO

/**
 * START OF SIMPLE QUERY RUN AND OUTPUT
 */

$query = "SELECT * FROM employees WHERE birth_date < '1955-01-01'";

//Running a simple select statement using mysql_* (the old deprecated way)
$results = mysql_query($query, $link);

while($row = mysql_fetch_assoc($results)) {
    echo $row['first_name'] . ' ' . $row['last_name'] . '<br />';
}

//Running a simple select statement using PDO (the new way)
//method 1:
foreach($dbh->query($query) as $row) {
    echo $row['first_name'] . ' ' . $row['last_name'] . '<br />';
}

//method 2:
$statement = $dbh->query($query);

while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['first_name'] . ' ' . $row['last_name'] . '<br />';
}

//method 3: (This will put all results into an associative array)
$statement = $dbh->query($query);
$resultsArray = $statement->fetchAll(PDO::FETCH_ASSOC);

As you can see with PDO we can accomplish running the query and looping through the results in a much more compact foreach loop if we wish. However, if you're used to the old way with mysql of querying, then looping through in a while loop it might be easiest for you to adopt the second method shown above for PDO. There is also a great fetchAll() function with PDO that will automatically put these items into an associative array for you if you were planning on using them later. This saves you a little bit of coding effort when you have result sets that you need to use multiple times.

How to use variables in a query and protect from SQL Injection

In the olden days of MySQL you really had to go through quite an ordeal to make sure that your user input was sanitized, otherwise you could end up with a Bobby Tables fiasco. Up to this point one might argue that the differences have been pretty negligible between the old way and the new, but I think here you'll see some clear advantages to using prepared statements over the old way of having PHP sanitize your queries for you.

/**
 * START OF QUERY WITH PARAMETERS
 * localhost/pdo.php?number=10001&salary=60117
 */

$employeeID = $_GET['number'];
$salaryID = $_GET['salary'];

//Query with parameters for mysql_* (the old deprecated way)
$results = mysql_query(sprintf("SELECT * FROM salaries WHERE emp_no='%s' AND salary='%s'",
        mysql_real_escape_string($employeeID),mysql_real_escape_string($salaryID))) or die(mysql_error());

$rows = array();
while($row = mysql_fetch_assoc($results)){
    $rows[] = $row;
}

//Query with parameters for PDO (the new way)
//method 1:
$statement = $dbh->prepare('SELECT * FROM salaries WHERE emp_no=? AND salary=?');
$statement->execute(array($employeeID, $salaryID));
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//method 2: Bind Value
$statement = $dbh->prepare('SELECT * FROM salaries WHERE emp_no=? AND salary=?');
$statement->bindValue(1, $employeeID, PDO::PARAM_INT);
$statement->bindParam(2, $salaryID, PDO::PARAM_INT);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//method 3: Named Placeholders using BindValue
$statement = $dbh->prepare('SELECT * FROM salaries WHERE emp_no=:empId AND salary=:salId');
$statement->bindValue(':empId', $employeeID, PDO::PARAM_INT);
$statement->bindValue(':salId', $salaryID, PDO::PARAM_INT);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//method 4: Named Placeholders binding on execute
$statement = $dbh->prepare('SELECT * FROM salaries WHERE emp_no=:empId AND salary=:salId');
$statement->execute(
    array(
        ':empId' => $employeeID,
        ':salId' => $salaryID
    )
);
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

As you can see there are several much better methods that are far more portable in syntax than the old way of using the mysql functions. Another great advantage is that all PDO functions behave the same way, so now if you migrate over to an oracle database or perhaps microsoft sql, you don't have to worry about changing how you sanitize your queries because all of the code remains the same. You can also see how in some cases the old way could get you cross eyed pretty quick if you were binding 10 or 15 parameters in a query, and the new way (especially with the named placeholders) it is easy to tell what variable belongs where.

Need Additional Help?

If you have an old application and would like someone to take a look at it to see if this breaking change might affect you or you just have questions you think we might be able to help you answer please feel free to contact ExchangeCore's development team by visiting our Web Development Page.