Difference Between PDO And MySQLi

We have two choices for accessing the database in PHP : MySQLi and PDO. We have already compared them with old mysql extension in Difference between mysql and pdo and Difference between MySQL and MySQLi. In this article, we will discuss difference between PDO and MySQLi on various feature like database support, stability and performance to find which is the better API.

Connection :

// 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');

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.

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());

Or link pdo drivers

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.

Named Parameters :
This is also an important feature that PDO has binding naming parameters is easier than using the numeric binding:

    $stmt = $pdo->prepare("SELECT * FROM data WHERE id=:id AND fname=:fname");
    $stmt->execute(array(':fname' => $fname, ':id' => $id));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

whereas in MySQLi way:

    $stmt = $mysqli->prepare("SELECT `name` FROM `users` WHERE `id` = ?");
    $stmt->bind_param('i', $id);
    $stmt->execute();

The question(?) mark parameter binding is shorter but it is not as flexible as named parameters, because developer must follow the order of parameter. So I prefer named parameters binding, unfortunately, MySQLi doesn’t support named parameters.

Security :
Both PDO and MySQLi provide SQL injection security, as long as developer follow them with features like escaping and parameter binding with prepared statements.

For example a hacker is trying to inject some malicious SQL through the ‘name’ HTTP query parameter (POST):

 $_POST['name'] = "'; DELETE FROM users; /*"

If we fail to escape, it will be added in query “as it is” , it will delete all rows from users table as PDO and MySQLi support multiple queries.

// PDO, “manual” escaping

$name = PDO::quote($_POST['name']);

$pdo->query("SELECT * FROM users WHERE name = $name");

// mysqli, “manual” escaping

$name = mysqli_real_escape_string($_POST['name']);

$mysqli->query("SELECT * FROM users WHERE name = '$name'");

PDO::quote() not only escapes the string, but it also quotes it. But mysqli_real_escape_string() will only escape the string, you will need to apply the quotes manually.

// PDO, prepared statement

$pdo->prepare('SELECT * FROM users WHERE name = :name');
$pdo->execute(array(':name' => $_POST['name']));

// mysqli, prepared statements

$query = $mysqli->prepare('SELECT * FROM users WHERE name = ?');
$query->bind_param('s', $_POST['name']);
$query->execute();

I always recommend you to use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().

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. You can see the benchmark test results here.

Conclusion :
PDO is better with support of different database drivers and named parameters. We can ignore the performance loss in PDO. In terms of security both are safe but developer should use them with they explained.

Related Tutorials