MYSQL TO MYSQLI CHEAT SHEET - Feb 9th, 2023


MySql to MySqli Cheat Sheet

According to Daniel Louwe, Technical Lead at Interactive Tools, "CMSB also now includes a Legacy MySQL Scanner plugin -
you can activate this plugin and then use it to run a scan both on your plugins and website files to locate other places
that need to be updated. Updating to the latest version of a plugin should fix any legacy MySQL issues there, however,
anything found in your website files may require some manual fixing."

Here's some more information from Interactive Tools

For each mysql_ function, find it in the Original MySQL API here:
http://php.net/manual/en/book.mysql.php

And then follow the link to the replacement function and copy the code replacing
$mysql-> with mysqli()->

I'll add fixes as I come across them.
Please email me with your conversion notes.

Here are some example search and replaces:

Search for: ........ Replace with:
--------------------------------------------------------------------------------
mysql_query( ........ mysqli()->query(
mysql_error( ........ mysqli()->error
mysql_ping( ........ mysqli()->ping(
mysql_select_db( ........ mysqli()->select_db(
mysql_server_info( ........; mysqli()->server_info
mysql_set_charset ........ mysqli()->set_charset(
mysql_insert_id ........ mysqli()->insert_id;
mysql_close( ........; mysqli()->close();
mysql_affected_rows ........ mysqli()->affected_rows
mysql_fetch_assoc($result) ........ $result->fetch_assoc()
mysql_fetch_row($result) ........ $result->fetch_row()

For any other mysql function find the original and replacement code in the PHP
manual online here: http://php.net/manual/en/book.mysql.php

Reference:
PHP Original MySQL API: http://php.net/manual/en/book.mysql.php
PHP MySQL Improved Extension: http://php.net/manual/en/book.mysqli.php
________________________________________
Some changes that I've made:

Change this:

$query = "SELECT DATE_FORMAT(date, '%M %Y') as dateAndYear, YEAR(date) as year, MONTH(date) as month FROM cmsb_blog
WHERE `hidden` = 0 GROUP BY dateAndYear ORDER BY date";

$result = mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
while ($record = mysql_fetch_assoc($result)):

To this:

$query = "SELECT DATE_FORMAT(date, '%M %Y') as dateAndYear, YEAR(date) as year, MONTH(date) as month FROM cmsb_blog
WHERE `hidden` = 0 GROUP BY dateAndYear ORDER BY date";

$result = mysqli()->query($query) or die("MySQL Error: ". htmlspecialchars(mysqli()->error) . "\n");
while ($record = $result->fetch_assoc()):


And this:

mysql_query(mysql_escapef("INSERT INTO {$TABLE_PREFIX}login_log SET


Becomes this:

mysqli()->query(mysql_escapef("INSERT INTO {$TABLE_PREFIX}login_log SET


And this:

mysql_query("INSERT INTO `{$TABLE_PREFIX}salon_listings` SET

becomes this:

mysqli()->query("INSERT INTO `{$TABLE_PREFIX}salon_listings` SET


And this:

mysql_query("UPDATE `{$TABLE_PREFIX}salon_listings` SET

becomes this:

mysqli()->query("UPDATE `{$TABLE_PREFIX}salon_listings` SET

And this:

mysql_query("UPDATE `{$TABLE_PREFIX}accounts` SET
expiresDate = expiresDate + INTERVAL 1 YEAR
WHERE num = '".mysql_escape( $CURRENT_USER['num'] )."'")
or die("MySQL Error:\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

Can also become this:

$colsToValues = array();
$colsToValues['updatedDate='] = 'NOW()';
$colsToValues['expiresDate='] = 'expiresDate + INTERVAL 1 YEAR';
mysql_update(accountsTable(), $CURRENT_USER['num'], null, $colsToValues);

According to Daniel Loewe at Interactive Tools,

You should be able to replace mysqli_insert_id() with mysqli()->insert_id;

A suitable replacement for mysql_real_escape_string() is mysql_escape().



The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.


Terms of Service