Difference between MySQL and MySQLi

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.

Calculate size of databases from information schema

I was working on a project where i need to work with database, suddenly a query raise in my mind , that is database size… yes How to calculate databases size with a single query. So I get to know and sharing this to you also

Mysql Query

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM   information_schema.tables GROUP  BY table_schema

Copy above query and paste in Sql tab of local mysql software like Xampp or Wampp. This will results with two columns named “DB Name” and “DB Size in MB”.

Screenshot:

mysql_databases

Preventing SQL Injection:

SQL injection is one of the most common vulnerabilities in applications on the web today. This article will show you how to prevent SQL injection on your website using Prepared Statements in PHP.

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc())
{
$name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

The LIKE Quandary:

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == %something_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");