Migrate from MySQL to MySQLi & vice versa

There have been many questions on how to migrate from MySQL to MySQLi. Well adding the letter i to mysql wouldn’t solve the problem while you are migrating. There is a change in the method of query. First of all you need to have a basic knowledge of MySQL to follow this post.

MySQL – Create a Table

$table = "CREATE TABLE IF NOT EXISTS Users (
		 		 id int(11) NOT NULL auto_increment,
				 username varchar(255) NOT NULL
                                            )";

mysql_query($table);

MySQLi – Create a Table

$link = mysqli_connect("localhost","username","password", "databasename");
$table = "CREATE TABLE IF NOT EXISTS Users (
		 		 id int(11) NOT NULL auto_increment,
				 username varchar(255) NOT NULL
                                            )";
$query = mysqli_query($link, $table);

The same $link has to be passed whenever you are using the MySQLi type mysqli_query()command. For example when you are using commands like INSERT, SELECT, DELETE,etc you have to pass the $link parameter shown above. The mysqli_query() defaultly expects two parameters to be passed. One is the $link paramter which connects to the database and the other is the query operation.

In case you do not provide the $link parameter in your query, then you will get an error like this while processing your page:
mysqli_query() expects at least 2 parameters

Also read: MySQL vs MySQLi which is better and What’s the difference – Explained!

Now moving onto other MySQL commands, like mysql_real_escape_string() the change in the MySQLi format can be seen below.

$demo = mysql_real_escape_string($email1);

$demo = mysqli_real_escape_string($link, $email1);

If the $link parameter isn’t passed through the mysqli_real_escape_string() then you will see the following error when you go to your page:
mysqli_real_escape_string() expects exactly 2 parameters

the mysql_error() command in MySQL and it’s equivalent in MySQLi can be seen below:

mysql_error();

mysqli_error($link);

If you do not pass the $link parameter then you are likely to see the following error:
mysqli_error() expects exactly 1 parameter, 0 given

the mysql_num_rows() command in MySQL and it’s equivalent in MySQLi can be seen below:

$sql = mysql_query("SELECT username FROM Users WHERE username='admin'"); 
$check = mysql_num_rows($sql);

$sql = mysqli_query($link, "SELECT username FROM Users WHERE username='admin'"); 
$check = mysqli_num_rows($sql);

the mysqli_fetch_array() command in MySQL and it’s equivalent in MySQLi can be seen below:

$sql = mysql_query("SELECT id, username, firstname, lastname FROM myMembers WHERE id='123' LIMIT 1");
$row = mysql_fetch_array($sql);

$sql = mysqli_query($link, "SELECT id, username, firstname, lastname FROM myMembers WHERE id='123' LIMIT 1");
$row = mysqli_fetch_array($sql);

So these are the major differences between MySQL and MySQLi commands. Hope you will find it easy to migrate from MySQL to MySQLi using these. The MySQLi commands are all in precedural form instead of Object-Oriented Style. If you have any queries regarding this process then please feel free to reach me through the comment section.

MySQL – Create a Table

MySQLi – Create a Table

Related