How to Convert MySQL to MySQLi

One of the most important developments in the PHP world was the backward compatibility break for the PHP MySQL extension, which leaves us with two methods to connect to the database: MySQLi and PDO.

In PHP 7, the MySQL extension is completely removed. Thus, in this article, I will discuss how to convert a MySQL extension into MySQLi. The first thing you should understand is that MySQL works as a resource whereas MySQLi works as a resource and an object. While you really do not need to know the technical differences, you must understand that these two are a lot different from each other.

MySQL to MySQLi Procedural Methods

I will first start with the most commonly used MySQL methods. The only thing that changed in the following method is the addition of "i" in MySQL and some changes of the positions of link and result in the parameter of the method.

Following are the popularly used methods of MySQL, followed by the equivalent methods in MySQLi.

mysql_affected_rows -> mysqli_affected_rows($link)

mysql_close -> mysqli_close($link)

mysql_data_seek -> mysqli_data_seek( $result, $offset)

mysql_errno -> mysqli_errno( $link)

mysql_error -> mysqli_error( $link)

mysql_fetch_array -> mysqli_fetch_array( $result, $type)

mysql_fetch_assoc -> mysqli_fetch_assoc( $result)

mysql_fetch_lengths -> mysqli_fetch_lengths( $result )

mysql_fetch_object -> mysqli_fetch_object( $result, $class, $params)

mysql_fetch_row -> mysqli_fetch_row( $result)

mysql_field_seek -> mysqli_field_seek( $result, $number)

mysql_free_result -> mysqli_free_result(result)

mysql_get_client_info -> mysqli_get_client_info( $link)

mysql_get_host_info -> mysqli_get_host_info( $link)

mysql_get_proto_info -> mysqli_get_proto_info( $link)

mysql_get_server_info -> mysqli_get_server_info( $link)

mysql_info -> mysqli_info( $link)

mysql_insert_id -> mysqli_insert_id( $link)

mysql_num_rows ->  mysqli_num_rows( $result)

mysql_ping -> mysqli_ping( $link)

mysql_query -> mysqli_query( $link, $query)

mysql_real_escape_string -> mysqli_real_escape_string( $link)

mysql_select_db - > mysqli_select_db( $link, $database)

mysql_set_charset -> mysqli_set_charset( $link, $charset)

mysql_stat -> mysqli_stat( $link)

mysql_thread_id -> mysqli_thread_id( $link)

If you want to convert your script from a MySQL extension to a MySQLi extension manually, you must start with the top of the script and start converting all the methods one by one.

For this, open the script in any text editor and use the find-and-replace tool to replace mysql_ with mysqli. But that's not all. You must manually check and verify the parameters of the method, as well.

To automate the process of converting the script from MySQL to MySQLi, I can use the following online tools. I will create a sample insertion and retrieval code and convert it using one of these tools.

Sample Code for Conversion

I am going to use the following database and code for testing the online conversion tools.

Database SQL

CREATE TABLE `info`.`student` 
  ( 
     `id`        INT NOT NULL auto_increment, 
     `name`      VARCHAR(40) NOT NULL, 
     `last name` VARCHAR(40) NOT NULL, 
     `email`     VARCHAR(40) NOT NULL, 
     PRIMARY KEY (`id`) 
  ) 
engine = innodb; 

Connection.php

<?php
$servername = "localhost" ;
$username = "root";
$pass = "";
$con = mysql_connect($servername,$username,$pass) or die("Problem occur in connection");
$db = mysql_select_db("info",$con);
?>

Insert.php

<?php
require_once("connection.php");
$query = "Insert into student values('','Saquib','Rizwan','saquib.rizwan@cloudways.com')";
$result = mysql_query($query);
if(isset($result))
{
 echo "Data has been inserted";
}
?>

Retrieve.php

<?php
require_once("connection.php");
$query = "select * from Student ";
$result = mysql_query($query);
while($data = mysql_fetch_array($result))
{
 echo "<br/><br/>";
 echo "ID : $data[0]";
 echo "Name : $data[1]  ";
 echo "Last Name : $data[2]  ";
 echo "Email : $data[3]  ";
}
?>

Here is the basic PHP code which uses MySQL extension to create the database connection, select database, create and execute a query and fetch data from the database.

Now I will convert the script using an online tool.

ICT Academie

In order to convert the code using ICT Academie, all you need to do is paste your code snippet enclosed in PHP opening and closing tags.

For example, if you want to see how mysql_connect() gets converted, paste <?php mysql_connect() ?>>".

Code conversion result from the ICT Academie for the above code is as follows.

Connection.php

<?php  
$servername = "localhost" ;  
$username = "root"; 
$pass = "";  
$con = ($GLOBALS["___mysqli_ston"] = mysqli_connect($servername, $username, $pass)) or die("Problem occur in connection");  
$db = ((bool)mysqli_query($con, "USE " . info));  
?> 

Insert.php

<?php  
require_once("connection.php"); 
$query = "Insert into student values('','Saquib','Rizwan','saquib.rizwan@cloudways.com')";  
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);  
if(isset($result)) 
{ 
    echo "Data has been inserted";  
} 
?> 

Retrieve.php

<?php  
require_once("connection.php"); 
$query = "select * from Student ";  
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);  
while($data = mysqli_fetch_array($result)) 
{ 
    echo "<br/><br/>";  
    echo "ID : $data[0]    "; 
    echo "Name : $data[1]  "; 
    echo "Last Name : $data[2]  "; 
    echo "Email : $data[3]  ";  
} 
?> 

To test the converted code, it is clear that all the MySQL methods have been successfully converted into MySQLi. It only changes the PHP code, but the result remains the same.

Note: Before starting the conversion process, create backups of all the files and test run all the code before live deployment. The converter only converts the code. It does not add any security measures to prevent SQL injections.