• support@answerspoint.com

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


What is mysqli ?. What is PDO?. What is difference between PDO vs mysqli and 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.

  • answered 1 year ago
  • Community  wiki


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

PDO has been introduced in PHP, and the project aims to make a common API for all the databases access, so in theory you should be able to migrate between RDMS without changing any code (if you don't use specific RDBM function in your queries), also object-oriented.

  • answered 1 year ago
  • Community  wiki


We have two choices for accessing the database in PHP : MySQLi and PDOIn this post, we will discuss difference between PDO and MySQLi on various feature like database support, stability and performance to find which is the better API.


// PDO

$pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password');

 // mysqli, procedural style

$mysqli = mysqli_connect('localhost.com','username','password','data');

// mysqli, object oriented style

$mysqli = new mysqli('localhost.com','username','password','data');

 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:


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. 

  • answered 1 year ago
  • Community  wiki

Your Answer

    Facebook Share        
  • asked 1 year ago
  • viewed 723 times
  • active 1 year ago

Hot Questions