How to migrate PHP mysql to mysqli extension

Recently, I had a to migrate a website that was running PHP 5.6 over to PHP 7. As usual, the first thing I did was to just copy the site over to the new host, and check the logs to see what the errors were. I saw a ton of messages like this:

PHP Fatal error:  Uncaught Error: Call to undefined function mysql_connect()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_query()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_num_rows()
...

I was really lucky! The main problem was that the site relied on database extensions for MySQL that have been deprecated since PHP 5.5, and were removed in PHP 7.

This turned out to be a pretty easy fix. You can check out the documentation for the new MySQLi improved extension over at PHP.net. Here are the steps that I took to rework the code so that the site functioned normally, again.

mysql_connect and mysql_select_db

The previous connection method was mysql_connect, called like this:

$conn = mysql_connect(mysql_server_name, username, password);

I replaced that with this:

$conn = mysqli_connect(mysql_server_name, username, password, database_name);

Since I could pass in the database name, I could then throw out an old call to <a href="https://www.php.net/manual/en/function.mysql-select-db.php">mysql_select_db</a> as well.

mysql_real_escape_string

<a href="https://www.php.net/manual/en/function.mysql-real-escape-string.php">mysql_real_escape_string</a>($str) was replaced with $conn-><a href="https://www.php.net/manual/en/mysqli.real-escape-string.php">real_escape_string</a>($str). I could have also used mysqli_real_escape_string($str).

mysql_query

$results = <a href="https://www.php.net/manual/en/function.mysql-query.php">mysql_query</a>($sql_query) was replaced with $results = $conn-><a href="https://www.php.net/manual/en/mysqli.query.php">query</a>($sql_query).

mysql_num_rows

<a href="https://www.php.net/manual/en/function.mysql-num-rows.php">mysql_num_rows</a>($results) was replaced with $results-><a href="https://www.php.net/manual/en/mysqli-result.num-rows.php">num_rows</a>.

mysql_fetch_assoc

$row = <a href="https://www.php.net/manual/en/function.mysql-fetch-assoc.php">mysql_fetch_assoc</a>($results) was replaced with $results-><a href="https://www.php.net/manual/en/mysqli-result.fetch-assoc.php">fetch_assoc</a>().

mysql_error

$err = <a href="https://www.php.net/manual/en/function.mysql-error.php">mysql_error</a>($conn) was replaced with $err = $conn-><a href="https://www.php.net/manual/en/mysqli.error.php">error</a>.

mysql_close

<a href="https://www.php.net/manual/en/function.mysql-close.php">mysql_close</a>($conn) was replaced with $conn-><a href="https://www.php.net/manual/en/mysqli.close.php">close</a>().

Aside from these problems with using the old MySQL extension, I noticed one other problem. There were a few messages in the PHP logs, like this:

PHP Notice: Undefined variable: blah

This happened when the variable had never been defined before it was used. For example, I saw code like this:

$myArr = getSomething($blah);
...

The variable $blah was only found once in that source code – it had never been initialized. So I just initialized it to NULL, and the notice went away. Probably it was a typo or copy/paste error. I also double-checked to make sure that NULL was an acceptable input, and not a lurking bug.

The PHP notice message didn’t do much harm, but it was cluttering up the logs, making it hard to see the real problems, so getting rid of it was a good thing. As a general rule, I like to get rid of messages like this. That way, if something goes wrong, it’s easier to find new problems by checking the logs.