With PHP 8 around the corner, it's probably a bit late to write about migrating the mysql_* functions that were last seen in PHP 5, but we all know how long old code sticks around.

The migration has to be gradual because management never has time for a full rewrite, or they say they do then axe the project half way through when the next shiny comes along. More importantly it has to work without having a separate connection for PDO, since doubling the amount of database connections can be a problem.

Most importantly, the new code has to be significantly easier to use than the old code, or developers will keep using mysql_real_escape_string for as long as possible.

The target code

Let's execute a query in mysql_:

$last_logged_in = new DateTime('yesterday');
$list_of_user_ids = [1, 2, 3];
$user_input = $_POST['search'];

$sql = 'SELECT u.* FROM users u WHERE last_logged_in > ';
$sql .= '"'.$last_logged_in->format('Y-m-d H:i:s').'" ';

$param = [];
foreach ($list_of_user_ids as $id) {
    $param[] = '"'.((int) $id).'"';
}
$sql .= ' AND u.id IN('.implode(',', $param).') ';

$sql .= ' AND u.name LIKE "'.mysql_real_escape_string($user_input).'"';

$res = mysql_query($sql);
$result = [];
while ($row = mysql_fetch_assoc($res)) {
    $result[] = $row;
}

What an unholy mess! This is the kind of stuff that made people hate PHP back in the day.

Let's rewrite that in PDO and see what it looks like:

$last_logged_in = new DateTime('yesterday');
$list_of_user_ids = [1, 2, 3];
$user_input = $_POST['search'];

$params = [$last_logged_in->format('Y-m-d H:i:s')];
$params = array_merge($params, $list_of_user_ids);
$params[] = $user_input;

$sql = '
    SELECT u.* FROM users u
    WHERE last_logged_in > ?
    AND u.id IN('.implode(',', array_fill(0, count($list_of_user_ids), '?')).')
    AND u.name LIKE ?';

$stmt = $db->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Much better. It's more legible and less likely to be injected. But it's not exactly pleasant to work with. This is what we're going to aim for:

$last_logged_in = new DateTime('yesterday');
$list_of_user_ids = [1, 2, 3];
$user_input = $_POST['search'];

$results = $db->fetchAll('
        SELECT u.* FROM users u
        WHERE last_logged_in > ?
        AND u.id IN(?)
        AND u.name LIKE ?
    ',
    [
        $last_logged_in,
        $list_of_user_ids,
        $db->unlike($user_input),
    ]
);

Now that's a clean query execution!

To Mysqli

First off we need our old code to not explode on PHP 7, otherwise there's not much point in any of this.

Essentially most mysql_ functions are exactly the same in mysqli, except you add the i and the connection object as the first parameter.

This allows you to have multiple connections running at the same time without having to call mysql_select_db to change the global database.

While you could find-replace through your whole codebase using $GLOBALS[...] as your connection parameter, you could just composer require dshafik/php7-mysql-shim and it will handle all of this for you.

Don't tell management it will work on PHP 7 until you've finished the job though!

To PDO

In order to go to PDO we need some sort of a wrapper that will use the mysqli connection but has the same API as PDO. This way we can rewrite old queries in new code without doubling the amount of connections we make and without breaking transactions.

Luckily, such a wrapper already exists.

doctrine/dbal is the abstraction layer underpinning the ORM they're more well known for. It's a drop-in replacement for PDO, but it supports mysqli, MSSQL, Oracle, everything that PDO supports, as well as some other ones you've never heard of.

For some absurd reason it doesn't have a driver for pg_ but it shouldn't be too hard to write one if you only have to support your own application.

You can either create the connection in DBAL and set the mysqli object in the shim, or create the connection in the shim and set the mysqli object in the DBAL connection.

For the former you'll need to put the connection into Dshafik\MySQL::$last_connection and replicate the sha1 connection hash (sha1($hostname.$username.$flags);) as the key for the Dshafik\MySQL::$connections array.

For the latter you'll need to extend the driver and override the constructor, because while DBAL allows you to send it a premade PDO connection, the individual drivers won't.

Now you can already use the PDO example and the mysql_ functions interchangeably.

While the fetchAll method will work, it won't automatically expand DateTime objects and arrays of parameters for you, and the unlike method was also custom. I wrote about how to implement that a while back.

Transactionals

One thing that DBAL does very well is transactionals. Transactionals allow you to wrap a piece of code in a closure and it will automatically create a transaction around it. If an exception is thrown it will roll back the transaction upon reaching the end of the closure.

The neat thing about this is that it keeps track of the nesting level, so you can nest these as many times as you want and it will always roll back if something goes wrong. Of course, you can't use this in conjunction with transactions started through mysql_ because they won't keep track of the nesting level, so they'll need to be replaced.

Also, while DBAL will throw exceptions if encountering an error like a unique constraint or syntax error, mysqli will not. So you'll still need to add error checking to any legacy functions that you want to work with these transactionals.

Alternatively, you could copy-paste the shim and rewrite it to throw exceptions whenever something goes wrong.

Finishing up

Eventually once you've moved all your mysql_ over to DBAL, you can either drop back to PDO if you've been strict about keeping to it, or (more likely) stick with DBAL since you've gotten used to the more pleasant API.

Total time to set it up should be a few hours. Total time to migrate all your queries? There's the question for you…