Differences and choices between PDO and MySQLi?

When using PHP to access the database, in addition to the database driver that comes with PHP, we generally have two better options: PDO and MySQLi. In the actual development process, to decide which one to choose, you must first have a relatively comprehensive understanding of both. This article analyzes their differences and compares multiple database type support, stability, performance, etc.

	PDO	< strong>MySQLi
Database support	12 different drivers	MySQL only
API	OOP	OOP + procedural
Connection	Easy	Easy
Named parameters	Yes	No
Object mapping	Yes	Yes
Prepared statements (client side)	Yes	No
Performance	Fast	Fast
Stored procedures	Yes	Yes

1. Connection

// PDO
$pdo = new PDO("mysql:host=localhost;dbname=database", 'username', 'password');
 
// mysqli, procedural way
$mysqli = mysqli_connect('localhost','username','password','database');
 
// mysqli, object oriented way
$mysqli = new mysqli('localhost','username','password','database');

2. API support

Both PDO and MySQLi provide APIs in an object-oriented form, but MySQLi also provides a process-oriented API, which is easier for novices to understand. If you are familiar with the native PHP mysql driver, you will find that it is easy to use the MySQLi interface to replace the original data access. The advantage of using PDO is that PDO supports multiple databases, while MySQLi only supports MySQL. Once you master it, you can use and connect multiple databases as you like.

3. Database support

The biggest advantage of PDO over MySQLi is that PDO supports many kinds of databases, while MySQLi only supports MySQLi. To see which databases PDO supports, use the following code:

var_dump(PDO::getAvailableDrivers());

What are the benefits of supporting multiple databases? When your program wants to change from mysql to sql server or oracle in the future, the advantages of PDO will be reflected, because changing the database is transparent to the program interface, and the php code changes are very small. If you are using MySQLi, then all users Everywhere in the database has to be rewritten, so I can only make such changes.

4. Named parameter support

PDO named parameters and parameter binding:

$params = array(':username' => 'test', ':email' => $mail, ':last_login' => time() - 3600);
     
$pdo->prepare('
    SELECT * FROM users
    WHERE username = :username
    AND email = :email
    AND last_login > :last_login');
     
$pdo->execute($params);

And MySQLi parameter binding:

$query = $mysqli->prepare('
    SELECT * FROM users
    WHERE username = ?
    AND email = ?
    AND last_login > ?');
     
$query->bind_param('sss', 'test', $mail, time() - 3600);
$query->execute();

From the comparison above, we can see that PDO binds values through named parameters, while MySQLi’s parameter binding binds values through the dot symbol “?” and strictly in the order of the question mark. In this way, although the code seems not as long as PDO’s corresponding name, one disadvantage is that the readability and maintainability are reduced. When the number of parameters is relatively small, it is not noticeable. When the number of parameters increases to more than 10 The case of one or more is more painful. You have to assign values one by one in the order of question marks. If one of them is wrong, the following ones will be wrong.

Unfortunately MySQLi does not support named parameter binding like PDO.

5. Object Mapping

Database-based development generally reads data from the database and then uses an object to carry the data. Both PDO and MySQLi support object mapping. Suppose there is a User class that has some properties corresponding to the database.

class User {
    public $id;
    public $first_name;
    public $last_name;
     
    public function info()
    {
        return '#'.$this->id.': '.$this->first_name.' '.$this->last_name;
    }
}

If there is no object mapping, we have to assign values to fields one by one after reading the data, which is very cumbersome.

Please see below the code for using objects between the two:

$query = "SELECT id, first_name, last_name FROM users";
     
//PDO
$result = $pdo->query($query);
$result->setFetchMode(PDO::FETCH_CLASS, 'User');
 
while ($user = $result->fetch()) {
    echo $user->info()."\
";
}
// MySQLI, procedural way
if ($result = mysqli_query($mysqli, $query)) {
    while ($user = mysqli_fetch_object($result, 'User')) {
        echo $user->info()."\
";
    }
}
// MySQLi, object oriented way
if ($result = $mysqli->query($query)) {
    while ($user = $result->fetch_object('User')) {
        echo $user->info()."\
";
    }
}

6. Security

Both can prevent sql injection. Let’s look at an example first.

$_GET['username'] = "'; DELETE FROM users; /*"

When the value of the username parameter entered by the user is the above value (“‘; DELETE FROM users; /*”), if you do not do any processing on this value, the user successfully injects the delete statement, then the records in the user table will be All were deleted.

6.1, manual escaping

// PDO, "manual" escaping
$username = PDO::quote($_GET['username']);
 
$pdo->query("SELECT * FROM users WHERE username = $username");
         
// mysqli, "manual" escaping
$username = mysqli_real_escape_string($_GET['username']);
 
$mysqli->query("SELECT * FROM users WHERE username = '$username'");

The above uses the functions that come with the PDO and MySQLi APIs to escape the obtained parameter values.

6.2, prepared statement parameter binding

The following is a more efficient and safe way to bind prepared statement parameters:

// PDO, prepared statement
$pdo->prepare('SELECT * FROM users WHERE username = :username');
$pdo->execute(array(':username' => $_GET['username']));
 
// mysqli, prepared statements
$query = $mysqli->prepare('SELECT * FROM users WHERE username = ?');
$query->bind_param('s', $_GET['username']);
$query->execute();

7. Performance

Since PDO can support other non-MySQL databases, and MySQLi is specifically designed for MySQL, the performance of MySQLi is slightly better than that of PDO. However, PDO and MySQLi are still not as fast as PHP’s native MySQL expansion. But this kind of performance comparison actually doesn’t mean much, because they are all quite fast. If your program performance requirements are not particularly demanding, all three can satisfy you. As for which one you want to choose, you have to weigh it based on your practical situation.

8. Summary

PDO’s biggest advantage is that it supports 12 database drivers and named parameter bindings. Through the above comparison, I believe you also know which one you will use to connect to the database in your own project?