Difference between MySQL and MySQLi


As we know the mysql_* functions are deprecated in PHP 5.5. So you should not use mysql_* functions in your application. We have better alternative of MySql is MySQL Improved that is MySQLi. In this article I am listing the difference between MySql and MySqli.

MySQLi: – The i stands for Improved. The MySQLi extension is designed to work with MySQL version 4.1.13 or newer. MySQLi introduced with PHP 5.0 and MySQLi takes advantage of the newer features of MySQL 5.

Connection:
To create a connection just instantiating a new instance of MySQLi and using a username with a password connecting to the database, as follow:

$db = new mysqli('localhost', 'user', 'pass', 'data');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

Select Query:
Now to get verified users from the users table:

$sql = "SELECT * FROM `users` WHERE `verified` = 1 ";

if(!$result = $db->query($sql)){
    die('There was an error in the query [' . $db->error . ']');
}

Now we have mysqli_result object in $result variable, we can do various things with this such as looping through the results and displaying the verified users.

Output results:
Using loop on results the username for each row will be the out as following:

while($row = $result->fetch_assoc()){
    echo $row['username'] . '\n';
}

Total Number of records:
Each mysqli_result object contain a variable $num_rows for total rows of result, we can access that variable by following:

<?php echo 'Total results: ' . $result->num_rows; ?>

Total Number of affected rows:
Sometimes when UPDATE / DELETE query is used you want to know how many rows are updated / deleted, you can get the total rows as follow with MySQLi object variable:

<?php echo 'Total rows updated: ' . $db->affected_rows; ?>

Move inside recordset:

$result->data_seek(10);

Free memory:
When you finished the current result set, it is optional but make a habit to free up system resources by using following code:

$result->free();

Escaping string:
Before inserting the data into database we have to escape special characters (NUL (ASCII 0), \n, \r, \, ‘, “, and Control-Z ) from string values (mainly to prevent SQL injection). You should use real_escape_string method like as below:

$db->real_escape_string('My name is "Khan"');

We have an alias function of it, which is shorter:

$db->escape_string('My name is "Khan"');

Now the string is safe to insert into your database using a query.

Close a connection:
To close a connection after using the database use following method:

 $db->close(); 

Prepared Statements:
A prepared statements is a precompiled SQL statement that can be executed multiple times by sending the data to the server. Prepared statements basically work by you using a ? placeholder where you want to substitute in a string, integer or double. Prepared statements don’t substitute the value into the SQL so the issues with SQL injections are mostly removed.

Prepare statement
The statement template is created by the application and sent to the DBMS. Some parameters are left unspecified by placeholders “?” like as below:

$stmt = $db->prepare("SELECT `name` FROM `users` WHERE `id` = ?");

Bind parameters
We use the method bind_param to bind a parameter. You need to specify the type of variable and then the variable – so for instance we’d use i as the first parameter (for integer), and our $id variable as the second:

$id = '2';
$stmt->bind_param('i', $id);

Execute statement
Just use execute method to the statement so that we can get the result:

 $stmt->execute(); 

Output results
Now we will bind the output variable $out_name using bind_result() method which will assign the result to variable. So we will use:

 $stmt->bind_result($out_name); 

Now we need to fetch the results using fetch() method, which returns values into the binded variables. We can use it as follow:

while($stmt->fetch()){
    echo $out_name . '\n';
}

Close statement:
After accessing the result we should free stored result memory for the statement using free_result() method.

 $stmt->free_result(); 

MySQLi supports Transactions:
The best part of MySQLi that it supports transactions. Transaction is a group of queries that executed not affect the database. If you have multiple insert that rely on each other and if any one fails, you can rollback the others. But the database must supports the transactions.

Auto commit disable
You need to disable the auto commit so the query won’t save to the database automatically. You can do this by set false:

 $db->autocommit(FALSE); 

Commit transaction
We can commit the transaction after all queries of the transaction ran by using commit() method:

 $db->commit(); 

Rollback
Now if any problem occures we can rollback with a simple method as follow:

 $db->rollback(); 

Have a look on an example of transaction from the php doc

<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} 

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();

?>

Procedural Style:
The mysqli api provides dual interface. It supports the procedural and object-oriented interface. Users may prefer the procedural interface as migrating from old mysql api. The procedural interface is similar to that of the old mysql extension. The function names differ only by prefix. Some mysqli functions take a connection handle as their first argument, whereas matching functions in the old mysql interface take it as an optional last argument.

In mysqli

$mysqli = mysqli_connect('localhost', 'user', 'pass', 'data');
$res = mysqli_query($mysqli, "SELECT * FROM `users` WHERE `verified` = 1 ");
$row = mysqli_fetch_assoc($res);
echo $row['username'];

in mysql

$mysql = mysql_connect('localhost', 'user', 'pass');
mysql_select_db("data");
$res = mysql_query("SELECT * FROM `users` WHERE `verified` = 1", $mysql);
$row = mysql_fetch_assoc($res);
echo $row['username'];

So we can list the features of MySQLi as below:

  • Object-oriented interface and procedural style
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions
  • Enhanced debugging capabilities

reference:

I hope it has explained the difference between MySQL and MySQLi.

difference between MySQL and MySQLi
difference between MySQL and MySQLi