MySQL to MySQLi
Helping You Through A MySQL Migration
Posted by Charlie Recksieck
on 2022-01-13
In particular, we want to talk about and show how to upgrade older MySQL code to MySQLI code.
Need To Upgrade
"MySQLi" stands for MySQL Improved. It started to be offered in PHP version 5.0 and offers a lot of benefits compared to previous MySQL, like persisant database connections (which allows things like rollbacks), good interaction with stored procedures in databases, among other things.
More importantly to you on a practical level, old-school MySQL reached end of life in February 2021 which is PHP verion 5.6. It will still work (as of this writing in January 2022) if your hosting solution still allows sites to be running with a PHP version as old as 5.6, but many host solutions will soon be requiring something like at least PHP version 7. Also, MySQL and PHP pre 5.6 have hit end of life, they aren’t supported which means there are no security patches, meaning that it’s not secure.
Long story short, MySQL has to be upgraded to MySQLi. Sorry. So if you have any such code, as we do in some legacy projects for clients, then this article is for you.
What Needs To Change
For the most part, any commands in your code that start with "mysql_" need to be converted to their MySQLi analog. There are more MySQL commands than just these (go to the always great W3 people for a more complete list.)
- mysql_connect
- mysql_select_db
The 2-part mysql_connect & mysql_select_db is now replaced by: mysqli_connect
- mysql_query
The critical query function is mysql_query
and is replaced by: $connection_variable->query
- mysql_numrows
Recordset count command mysql_numrows is replaced by: $query_result_variable>num_rows;
- mysql_result
The actual values retrieval command of mysql_result is replaced in a couple different ways
* Multiple Rows: $query_result_variable>fetch_assoc()) ... then each row properties can be retrieved
* Single Rows: $row = $query_result_variable->fetch_row();
Working Example - MySQL Code vs. Newer MySQLi Code
Below is some code similar to what powers this very blog post that you’re reading.
MySQL (old):
// connect to the database
$connDB=mysql_connect ("localhost", "DB_credentials", "DB_password");
if (!$connDB)
die(’Could not connect: ’ . mysql_error());
// get the most recent blog posts from database
$PostsQuery = "SELECT * FROM `TableName` WHERE `IsActive` = 1 ORDER BY `PostDate` DESC LIMIT 6";
$rsRecent=mysql_query($PostsQuery);
$numRecentPosts=mysql_numrows($rsRecent);
// loop through results ...
$intCounter=0;
while ($intCounter < $numRecentPosts) {
// get the data
$blogID = mysql_result($rsRecent, $intCounter, "IndexID");
$blogTitle = mysql_result($rsRecent, $intCounter, "ArticleTitle");
$blogSubtitle = mysql_result($rsRecent, $intCounter, "ArticleSubtitle");
// etc. similar for other fields
echo "{" . $blogID . ") " . $blogTitle . " [" . $blogSubtitle . "]\n";
}
MySQLi (new):
// connect to the database
$connDB = mysqli_connect("localhost","DB_credentials","DB_password","DB_name");
if (!$connDB)
die(’Could not connect: ’ . mysql_error());
// get the most recent blog posts from database
$PostsQuery = "SELECT * FROM `TableName` WHERE `IsActive` = 1 ORDER BY `PostDate` DESC LIMIT 6";
$rsRecent = $connDB->query($PostsQuery);
$numRecentPosts = $rsRecent>num_rows;
// loop through results ...
if ($numRecentPosts > 0) {
// get data of each row
while($rowCurrent = $rsRecent -> fetch_assoc()) {
$blogID = $rowCurrent["IndexID"];
$blogTitle = $rowCurrent["ArticleTitle"];
$blogSubtitle = $rowCurrent["ArticleSubtitle"];
echo "{" . $blogID . ") " . $blogTitle . " - " . $blogSubtitle . "]\n";
// in normal code you could use the $rowCurrent["ArticleTitle"] values directly
// instead of setting $blogTitle variable, pros & cons depending on code
}
}
Search & Replace
How much can you search & replace ... or automatically upgrade quickly throughout PHP? The bad news is that you can’t run a magic upgrade wand or particular upgrade software to do all of your conversions.
As you can see, the old "connect" command had 3 parameters and a separate database name command, whereas the newer one combines both together and couldn’t be carried out by a search and replace. The old query and the looping through results functions similarly need massaging.
During a conversion of a full site’s worth of PHP code, once you start you do get a little faster at this. We just went through this with a few sites, so if you wanted to outsource this to a software firm like us, it’s conceivably much worth the cost.
But do not put this off any longer if you have already. Someday, someway, you will NOT be able to use old MySQL anymore. When the rug gets pulled out from under you, then you’ll have real trouble. In that scenario your site(s) will stop working and it’s gonna take you a week or two to get back online. I think we all can agree how unacceptable that is.
PDO vs MySQLi
While we’re on the subject of "portability", make sure you also know that PDO (PHP Data Objects) accomplish what MySQLi does. They were both introduced early in PHP version 5 releases. But the difference is that PDO is written in a way where your queries and statements could more easily be moved from PHP to other languages.
Here’s a great article that makes the case for PDO: https://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059
We’re not trying to shame you for going with MySQL previously instead of PDO if you had the chance. But we mention it here especially because if you’re having to migrate code from MySQL before it’s too late, then you should at least consider migrating that code from MySQL to PDO now instead of going from MySQL to MySQLi.
That said, MySQLi as it currently exists will be supported at the very least through 2025 with no end-of-life date announcements yet. Realistically, that means something like a decade before these end of life issues will really hit you. 10 years in code is kind of an eternity.
