8 steps to convert your PHP code from mysql to mysqli

This is a follow up post to point 2 of my earlier post on this subject. This post has been created to help convert your code to the latest PHP mysqli library in order to run on PHP version 7 and on wards.

Read through the 8 points below. For each point I have mentioned how its done in mysql and what you need to do to change it to mysqli. In some points you can do a simple replace but in other places you will need to make considerable changes to the code to make it work.

1. Connecting to mysql and then selecting database

mysql library

$Cn = mysql_connect($DBHost, $DBUser, $DBPass) ;
if (!$Cn) {
die(‘Error: ‘ . mysql_error());
}
$db = mysql_select_db($DBName, $Cn);
if (!$db) {
die (‘Error: ‘ . mysql_error());
}

mysqli library

$Cn = mysqli_connect($DBHost, $DBUser, $DBPass, $DBName);
if (!$Cn_new) {
echo “Error: Unable to connect to MySQL.” . PHP_EOL;
echo “Debugging errno: ” . mysqli_connect_errno() . PHP_EOL;
echo “Debugging error: ” . mysqli_connect_error() . PHP_EOL;
exit;
}

2. Executing query

mysql library

$sql = “select * from employee where 1 = 1 “;
$rs = mysql_query($sql);

mysqli library

$sql = “select * from employee where 1 = 1 “;
$rs = mysqli_query($Cn,$sql);

3. Returning the number of records returned by the query

mysql library

mysql_num_rows($rs);

mysqli library

mysqli_num_rows($rs);

4. Looping through the recordset

mysql library

while($data = mysql_fetch_array($rs)) {
echo $data[‘fname’] . “
“;
}

mysqli library

while($data = mysqli_fetch_array($rs)) {
echo $data[‘fname’] . “
“;
}

5. Returning the last autonumber generated by the database after an INSERT query

mysql library

mysql_insert_id();

mysqli library

mysqli_insert_id($Cn_new);

6. Display the error message generated after executing a query

mysql library

$sql = “select * from employee where 1 = 1 “;
$rs = mysql_query($sql) or die (mysql_error());

mysqli library

$sql = “select * from employee where 1 = 1 “;
$rs = mysqli_query($Cn,$sql) or die (mysqli_error($Cn));

7. Where you are unsure about the number of columns in a particular result set

mysql library

$sql_info = ” select * from employee where id = “. $empid;
$rs_info = mysql_query($sql_info) or die(mysql_error());
$data_info = mysql_fetch_array($rs_info);

$numTotField = mysqli_num_fields($rs_info);

$sql_tm = “”;
for($i = 0; $i < $numTotField; $i++) { $fldname = mysql_field_name($rs_info, $i); $sql_tm .= mysql_field_name($rs_info,$i) . " = '". addslashes($data_info[$i]) ."',"; } $sql_ins_tm = " insert into employee_new set ". $sql_tm; mysql_query($sql_ins_tm) or die(mysql_error());

mysqli library

$sql_info = ” select * from employee where id = “. $empid;
$rs_info = mysqli_query($Cn_new,$sql_info) or die(mysqli_error($Cn_new));
$data_info = mysqli_fetch_array($rs_info);

$arrFields = mysqli_fetch_fields($rs_info);

$sql_tm = “”;
for($i = 0; $i < count($arrFields); $i++) { $fldname = $arrFields[$i]->name;
$sql_tm .= $fldname . ” = ‘”. addslashes($data_info[$i]) .”‘,”;
}

$sql_ins_tm = ” insert into employee_new set “. $sql_tm;
mysqli_query($Cn_new,$sql_ins_tm) or die(mysqli_error($Cn_new));

8. Reading a value from one column

mysql library