What is mysqLi and PDO, how to use it...?

MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements.

PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.

I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary.

 Database Support :

The main advantage of PDO over MySQLi is its database driver support. PDO supports 12 different drivers and MySQLi supports MySQL only.

To get the list of all drivers that PDO supports, use following code:

	
var_dump(PDO::getAvailableDrivers());

When you require to switch your project to use another database, in PDO you’ll have to change the connection string and a few queries – if they use methods which are not supported by new database. But in MySQLi, you will have to rewrite every part of code that included queries.

API Support :
Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like.

Object Mapping :
A really nice thing with PDO is you can fetch the data, injecting it automatically in an object. We have a Student class with some properties, which match field names from a database :

class Student {
 
    public $id;
    public $first_name;
    public $last_name
 
    public function getFullName() {
        return $this->first_name.' '.$this->last_name
    }
}

Without using object mapping, we need to fill each field’s value to use getFullName() method correctly. Object mapping allows us to predefine these properties before the object is constructed!

$query = "SELECT id, first_name, last_name FROM student";
     
// PDO
$result = $pdo->query($query);
$result->setFetchMode(PDO::FETCH_INTO, new Student);
  
while ($student = $result->fetch()) {
   echo $student->getFullName()."\n";
}
 
// MySQLI, procedural way
if ($result = mysqli_query($mysqli, $query)) {
   while ($student = mysqli_fetch_object($result, 'Student')) {
      echo $student->getFullName()."\n";
   }
}
// MySQLi, object oriented way
if ($result = $mysqli->query($query)) {
   while ($student = $result->fetch_object('Student')) {
      echo $student->getFullName()."\n";
   }
}

Performance :
Both PDO and MySQLi are quite fast. For selects, MySQLi was about 2.5% faster for non-prepared statements and about 6.7% faster for prepared statements. Old MySQL extension is faster than both of these.