What are the differences and choices between PDO and mysqli?

When using PHP to access the database, in addition to PHP's own database-driven, we generally have two more good choices: PDO and mysqli. In the actual development process to decide which one to choose the first to have a more comprehensive understanding of the two. This paper analyzes the different points of them, and compares the support, stability, performance and so on of multi-database type.

	PDO	mysqli
Database support	different drivers	MySQL only< /td>
API	OOP	OOP + procedural
connection	Easy	Easy
Named Parameters td>	Yes	No
Object mapping	Yes	yes
Prepared statements  (client side)	Yes	No
performance	Fast	Fast
St ORed procedures	Yes	Yes

One, the connection

// PDO $pdo New PDO ("Mysql:host=localhost;dbname=database", ' username ', ' password '//  mysqli, procedural  $mysqlimysqli_connect(' localhost ', ' username ', ' password ', ' database '//  mysqli, object oriented$mysqlinew mysqli (' localhost ', ' username ', ' Password ', ' database ');

Second, API support

Both PDO and Mysqli provide APIs in object-oriented form, but Mysqli also provides a process-oriented API that is easier to understand for beginners. If you are familiar with native PHP MySQL drivers, you will find it easy to use the Mysqli interface to replace the original data access. The advantage of PDO is that PDO supports multiple databases, while mysqli only supports MySQL, but you have the ability to connect to multiple databases as you like.

Third, the support of the database

The biggest advantage of PDO over Mysqli is that PDO supports a wide variety 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 later wants to change from MySQL to SQL Server or Oracle, the advantages of PDO can be reflected, because the Exchange database for the program interface is transparent, PHP code changes are very small, if you are using mysqli, then all the use of the database to rewrite the place, Such a change I can only hehe.

Four, named parameter support

PDO named parameters and parameter bindings:

$params Array $mail  Time ()-3600);      $pdo->prepare ('    SELECT * from users    WHERE username =: username and    email =: Email    and Last_login >: Last_login');      $pdo->execute ($params);

and the mysqli parameter binding:

$query $mysqli->prepare ('    SELECT * from users    WHERE username =?    and email =?    and Last_login >?' );      $query $mail  Time ()-3600); $query->execute ();

We can see from the above that PDO is bound by a named parameter to the value, while the MYSQLI parameter binding is through the dot character "?". And bind the values strictly in the order of the question mark. This way although the code does not seem to be the same as the PDO by name, but there is a bad place is the readability and maintainability are reduced, the number of parameters is less than the time when the parameter is more than 10 or more of the situation is more painful, you have to be in the order of question marks to a corresponding to the assignment, In case one of them is wrong, the back is followed by the wrong person.

Unfortunately, Mysqli does not support named parameter bindings such as PDO.

V. Object mapping (Objects Mapping)

Database-based development typically reads data from a database and then hosts the data with an object. Both PDO and mysqli support object mapping, assuming there is a user class that has some properties that correspond 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 read the data after the assignment of a field, which is cumbersome.

Here's a look at the code that uses the object:

$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 (). " \ n ";}//mysqli, proceduralif($result=Mysqli_query($mysqli,$query)) {     while($user=Mysqli_fetch_object($result, ' User ')) {        Echo $user->info (). " \ n "; }}//mysqli, Object orientedif($result=$mysqli->query ($query)) {     while($user=$result->fetch_object (' User ')) {        Echo $user->info (). " \ n "; }}

Vi. Security

Both can prevent SQL injection. Let's look at an example first.

$_get [' username '] = "'; DELETE from users; /*"

When the user enters the value of the username parameter as the value above ("'; DELETE from users; /* "), if you do not do any processing of this value, the user will successfully inject the DELETE statement, then all the records of the users table will be deleted.

6.1. Manual Escape

// PDO, "manual" Escaping $username = pdo::quote ($_get[' username '$pdo$username");          // mysqli, "manual" Escaping $username mysqli_real_escape_string ($_get[' username '$mysqli->query ("SELECT * from users WHERE username = '$ Username' ");

The functions of the PDO and Mysqli APIs are used to escape the values of the obtained parameters.

6.2. Prepared Statement parameter binding

The

below recommends a more efficient and secure way to bind prepared statement parameters:

// PDO, prepared statement $pdo->prepare (' SELECT * from users WHERE username =: username '); $pdo->execute (array$_get[' username '//  mysqli, prepared Statements$query$mysqli->prepare (' SELECT * from users WHERE username =? ' ); $query $_get [' username ']); $query->execute ();

Vii.. Performance

Since PDO is capable of supporting other non-MySQL databases, and mysqli is specifically designed for MySQL, the mysqli is slightly better than PDO performance. But PDO and mysqli are still not php native MySQL extension fast. But this performance comparison is not very significant, because they are quite fast, if your program performance requirements are not particularly harsh, the three can meet you. As for which one you want to choose, you have to weigh the actual situation.

Viii. Summary

PDO supports 12 types of database-driven and named-parameter bindings is its greatest advantage, and by contrast above, I'm sure you know what kind of database you will use to connect to your project?

What are the differences and choices between PDO and mysqli?