Difference between MySQL and MySQLi

Posted on by

Basically there is no major difference between Mysql and Mysqli. Mysqli is nothing but an improved extension of Mysql.

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

Features :

  • Support for Prepared Statements
  • Object-oriented interface
  • Support for Transactions
  • Support for Multiple Statements
  • Enhanced debugging capabilities
  • Embedded server support

Connectivity in our project:
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('servername', 'username', 'password', 'databasename');

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

Where $db is object for connection mysqli queries. Some various query examples are:

Select Query:

$sql = "SELECT * FROM `tablename` order by id DESC";
$result = $db->mysqli_query($sql);
while($row = $result->mysqli_fetch_array())
{
echo $row['name'];
}

Delete Query

$sql="delete from tablename where eID='$eID'";
$db->mysqli_query($sql);

Another Way of writing mysqli query
You can write mysql query without object pointing. See below

Select Query:

$sql = "SELECT * FROM `tablename` order by id DESC";
$result = mysqli_query($db,$sql);
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
echo $row['name'];
}

Delete Query

$sql="delete from tablename where eID='$eID'";
mysqli_query($db,$sql);

Use of databse object inside member function of a class
I am creating a function for deleting rows. You can use $db object inside a member function by defining as global.

See below example:

function deleteBlock($eID)
{
 global $db;
 $sql="delete from tablename where eID='$eID'";
 mysqli_query($db,$sql);
}

Use same as other queries of mysql.