How to convert mysql to mysqli

MySQL is used almost in all database related PHP projects. As PHP supports MySQL using a PHP extension. But old MySQL extension deprecated after the release of PHP 5.5.0. When PHP 5.5.0 came in existence MySQLi was introduced which is MySQL improved.

As old MySQL extension and Syntax in no longer supported for latest PHP versions, all developers have moved to MySQLi and PDO. Here i will help you learn, how to upgrade your old PHP projects running on versions below PHP 5.5.0. In this tutorial you will come to know about changes to be done in your MySQL syntax for making it compatible with PHP 5.5.0 and above.

MySQLi Conversion

Here i will mention the changes need to be done with following MySQL Syntax:

mysql_connect()
mysql_select_db()
mysql_query()
mysql_num_rows()
mysql_fetch_array()

Establish connection

As in MySQL we were using mysql_connect() function to establish connection with server. But in MySQLi we will use mysqli_connect() to establish connection. I have written syntax for both old and new version of MySQL simply copy and paste in your project:

/* MySQL Format */
$host="host_name"; // Name of the hosting server
$username="username"; // Your MySQL Username
$password="password"; // Your MySQL Password
$db="database"; // Database Name
mysql_connect($host,$username,$password); // Establishing connection
mysql_select_db($db); // Selecting Database

/* MySQLi Format */
$host="host_name"; // Name of the hosting server
$username="username"; // Your MySQL Username
$password="password"; // Your MySQL Password
$db="database"; // Database Name
$con=mysqli_connect($host,$username,$password,$db); // Establishing connection and selecting database


Running query

In MySQL we were using mysql_query() function which uses Query String as parameter to run any query. But in MySQLi we will use mysqli_query() which require two parameters i.e Connection String and Query String:

$sql="Select * from users where id='1'";
$result=mysql_query($sql); // Running Query in MySQL

$sql="Select * from users where id='1'";
$result=mysqli_query($con,$sql) // Running Query in MySQLi format

Checking number of rows in the result set.

In MySQL we were using mysql_num_rows() function which uses Result of query String as parameter and return number of rows in Result Set. In MySQLi the parameter is same i.e Result of query String, only the function calling is changed to mysqli_num_rows() :

/* MySQL Format */
mysql_num_rows($result); // Returns number of rows in the result set.



/* MySQLi Format */
mysqli_num_rows($result); // Returns number of rows in the result set.

Fetching result in form of array

In MySQL we were using mysql_fetch_array() to fetch result from Result Set to array. In MySQLi the parameter is same, i.e Result of query String, only the function calling is changed to mysqli_fetch_array() :

/* MySQL Format*/

while($row=mysql_fetch_array($result)){
 echo $row['id'];
 echo $row['name'];
}

/* MySQLi Format*/
while($row=mysqli_fetch_array($result)){
 echo $row['id'];
 echo $row['name'];
}

Thank You for reading my Tutorial. I hope that this tutorial help you understand the basic changes that are to be done in MySQL syntax to convert it to MySQLi . If you find any issue or have suggestion related to this tutorial the please write it below in the comment box.

One Reply to “How to convert mysql to mysqli”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.