Which one does PDO vs. Mysqli choose? (PDO vs. Mysqli:which should you use?) -Reprint

When you access data in PHP, you choose Mysqli and PDO, what should you know before you choose?

This article will describe the different points of the two approaches, database support, stability, performance, and so on.

Overview

	Pdo	Mysqli
Database Support	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

Link

Here are two ways to connect to a database

[PHP]View Plaincopy

2. Pdo
4. $pdo = New PDO ("Mysql:host=localhost;dbname=database", ' username ', ' password ');
8. Mysqli, procedural
10. $mysqli = mysqli_connect (' localhost ',' username ',' password ',' database ');
14. Mysqli, Object oriented
16. $mysqli = new mysqli (' localhost ',' username ',' password ',' database ');

(Note that these two connections will run through the full text)

API support

Both PDO and mysqli provide an object-oriented API, but Mysqli also provides a process-oriented API (which is functional) so it's easy to understand for beginners, and if you use the original MySQL API, migrating to Mysqli is also easy. On the other hand, once you've chosen PDO, you can use it on any database you want to use.

Database support

The core advantage of PDO over Mysqli is the drive support of the database. While writing this article, PDO supports 12 database drivers, while mysqli only supports MySQL.

The following code allows you to print out the database driver supported by the current PDO

[PHP]View Plaincopy

1. Var_dump (Pdo::getavailabledrivers ());

What does that mean?

If you choose to use PDO, when you need to change the database, when you encounter a non-existent or unsupported method, you only need to change the connection string, and some query statements, and mysqli, you need to re-all the query and connection method.

Name-type parameters

This is a very important feature of PDO, and it is easier to use name-based parameters than digital parameters.

[PHP]View Plaincopy

2. $params = Array (': username ' = ' = ' test ', ': email ' + $mail, ': Last_login ' + time ()-3600)  ;
6. $pdo->prepare ('
8. SELECT * from users
10. WHERE username =: username
12. and email =: Email
14. and Last_login >: Last_login ');
18. $pdo->execute ($params);

And the way of mysqli:

[PHP]View Plaincopy

1. $query = $mysqli->prepare ('
2. SELECT * from users
3. WHERE username =?
4. and email =?
5. and Last_login >? ');
7. $query->bind_param (' sss ', ' test ', $mail, Time ()-3600);
8. $query->execute ();

This question mark (?) The binding parameters appear to be short, but there is a lack of flexibility compared to the name parameter, and forcing the developer to guarantee the order of the parameters, sometimes making people feel very sore.

And unfortunately, Mysqli does not support name-type parameters.

Object Mappings

Both PDO and mysqli can map the results to objects. The following customizes a user class and some properties, and the fields correspond to the table field of the database.

[PHP]View Plaincopy

2. Class User {
4. public $id;
6. public $first _name;
8. public $last _name;
12. Public function info ()
14. {
16. return ' # '. $this->id. ': '. $this->first_name. ".  $this->last_name;
18. }
20. }

If you do not use object mapping, you should manually assign a value to the property before using the Inof () method, or assign a value at initialization time.

And the object map can be used to directly complete

[PHP]View Plaincopy

2. $query = "SELECT ID, first_name, last_name from users";
6. Pdo
8. $result = $pdo->query ($query);
10. $result->setfetchmode (Pdo::fetch_class, ' User ');
14. while ($user = $result->fetch ()) {
16. echo $user->info ()."  \ n ";
18. }
20. Mysqli, procedural
22. if ($result = Mysqli_query ($mysqli, $query)) {
24. while ($user = Mysqli_fetch_object ($result, ' user ')} {
26. echo $user->info ()."  \ n ";
28. }
30. }
32. Mysqli, Object oriented
34. if ($result = $mysqli->query ($query)) {
36. While ($user = $result->fetch_object (' user ')) {
38. echo $user->info ()."  \ n ";
40. }
42. }

Security issues

The most common of course is SQL injection. Both of these ways of connecting data provide a security mechanism.

The following is a simple statement injected by the $_get method

[PHP]View Plaincopy

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

If we do not deal with this parameter, the problem is obvious. And both PDO and MYSQLI support multiple queries. This may result in some data being deleted.

Data processing for $_get

[PHP]View Plaincopy

2. PDO, "manual" escaping
4. $username = Pdo::quote ($_get[' username ');
8. $pdo->query ("SELECT * from users WHERE username = $username");
12. mysqli, "manual" escaping
14. $username = mysqli_real_escape_string ($_get[' username ');
18. $mysqli->query ("SELECT * from users WHERE username = ' $username '");

As can be seen from the above code, pdo::quote not only escaped the string, but also added single quotes, and mysqli just escaped the string, you need to manually add a single quotation mark.

Here is the way prepared statements query

[PHP]View Plaincopy

2. PDO, prepared statement
4. $pdo->prepare (' SELECT * from users WHERE username =: username ');
6. $pdo->execute (Array (': username ' = $_get[' username '));
10. MYSQLI, prepared statements
12. $query = $mysqli->prepare (' SELECT * from users WHERE username =? ');
14. $query->bind_param (' s ', $_get[' username ');
16. $query->execute ();

It is recommended to use prepared statements to bind queries instead of Pdo::quote () and mysqli_real_escape_string ().

Performance

Both PDO and mysqli have very good performance. In the non-prepared statements benchmark, the mysqli is slightly faster by 2.5%, while prepared statements is 6.5%, which can be said to be irrelevant for performance. If you really care about this little bit of performance, and your own MySQL extension is faster than both, you can consider it.

Summarize

Finally, the synthesis of PDO wins in this comparison, supporting 12 different database drivers (18 different databases) and ........ ..... For the above mentioned ...

So the conclusion is: If you are still using mysqli, you can consider changing it.

Original link Address reference:

http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/

Which one does PDO vs. Mysqli choose? (PDO vs. Mysqli:which should you use?) -Reprint