How To Convert from mysql_ to mysqli_ in WordPress Plugins Easily

Author: erics, Posted August 24th, 2016 at 5:16:27pm

As PHP deprecates old functions, sometimes code maintenance changes become required for long-running sites. As of PHP 5.5, the MySQL functions are deprecated and are removed in PHP 7!

I recently had to convert multiple sites to mysqli PHP functions because a new server was running PHP 5.6 and the old server was on PHP 5.1

This method is a shortcut and one should really use the native WordPress interfaces to the database as illustrated here:
https://codex.wordpress.org/Class_Reference/wpdb

Here are some examples of things I did to convert.

First, add these two replacement functions to your code somewhere:

A key difference between the mysql_ functions and the mysqli_ functions, is that the new mysqli functions often require a database handle to be provided to the function, where the old mysql_ functions did not.
Search for all instances of “mysql_” in your code. Add the following two lines of code above your existing mysql_ commands to give you access to the needed WordPress database handle:

global $wpdb;
$dbh = $wpdb->dbh;


Then add the database handle variable into the needed locations and change the command to mysqli_ – for example:
$result = mysql_query($sql);
becomes
$result = mysqli_query($dbh,$sql);

These are the commands I changed that needed the database handle:
$mysqliQueryResult = mysqli_query($dbh,$sql);
$error = mysqli_error($dbh);
$errno = mysqli_errno($dbh);
$escaped = mysqli_real_escape_string($dbh, "yourString");

These are the commands that simply needed to be converted to mysqli_ – for example:

$numFields = mysqli_num_fields($result);
while ($row = mysqli_fetch_assoc($result)) {}
$columnName = mysqli_field_name($result , $i);
$data = mysqli_result($result, 0);
$obj = mysqli_fetch_object($mysqliQueryResult);


This is NOT a complete list, just the items I had to address. As always, YMMV…

UPDATE 20191130
You MUST change all occurrences of MYSQL_ASSOC to MYSQLI_ASSOC, too!

References:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
http://stackoverflow.com/questions/14629636/mysql-field-name-to-the-new-mysqli
http://mariolurig.com/coding/mysqli_result-function-to-match-mysql_result/

Categories: How-To's, Technology Tags: convert, mysql, mysqli, php, PHP5.5, PHP5.6, PHP55, PHP56, WordPress