002 - Differences and choices between PDO and MySQLi-PHP Tutorial-php.cn

Table of Contents

1. Connection

7. Performance

8. Summary

Home

Backend Development

PHP Tutorial

002 - Differences and choices between PDO and MySQLi

不言

Apr 08, 2018 pm 02:37 PM

mysqli the difference choose

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 them in terms of multi-database type support, stability, performance, etc.

12 different driversMySQL onlyOOPOOP proceduralEasyEasyYesNoYesYesYesNo##PerformanceStored procedures

1. Connection

002 - Differences and choices between PDO and MySQLi ##

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

Copy after login

002 - Differences and choices between PDO and MySQLi

2. API support

Both PDO and MySQLi provide API in object-oriented form, but at the same time MySQLi also provides process-oriented API. The format is easier to understand for novices. 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());

Copy after login

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 parameters support

PDO named parameters and parameter binding:

002 - Differences and choices between PDO and MySQLi

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

Copy after login

002 - Differences and choices between PDO and MySQLi

And MySQLi parameter binding:

002 - Differences and choices between PDO and MySQLi

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

Copy after login

002 - Differences and choices between PDO and MySQLi

We can see from the above comparison that PDO binds values through named parameters, while MySQLi’s parameter binding is Values are bound using the dot point character "?" and strictly in the order of this 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.

002 - Differences and choices between PDO and MySQLi

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

Copy after login

002 - Differences and choices between PDO and MySQLi

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 the code of the two objects below:

002 - Differences and choices between PDO and MySQLi ##

$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, procedural wayif ($result = mysqli_query($mysqli, $query)) {    while ($user = mysqli_fetch_object($result, 'User')) {        echo $user->info()."\n";
    }
}// MySQLi, object oriented wayif ($result = $mysqli->query($query)) {    while ($user = $result->fetch_object('User')) {        echo $user->info()."\n";
    }
}

Copy after login

002 - Differences and choices between PDO and MySQLi 6. Security

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

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

Copy after login

When the value of the username parameter entered by the user is the above value ("'; DELETE FROM users; /*"), if you do not set this value After doing any processing, the user successfully injects the delete statement, and all records in the user table will be 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'");

Copy after login

002 - Differences and choices between PDO and MySQLi 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();

Copy after login

7. Performance

Since PDO can support other non-MySQL databases, and MySQLi is specially 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 supports 12 kinds of database drivers and named parameter binding is its biggest advantage. Through the above comparison, I believe you also know what you will use in your own project Which one is connected to the database?

Related recommendations:

001 - Detailed analysis of PDO usage

	PDO	##MySQLi
Database support
API
Connection
Named parameters
Object mapping
Prepared statements (client side)
Fast	Fast
Yes	Yes

The above is the detailed content of 002 - Differences and choices between PDO and MySQLi. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

What's New in Windows 11 KB5054979 & How to Fix Update Issues

3 weeks ago By DDD

How to fix KB5055523 fails to install in Windows 11?

2 weeks ago By DDD

InZoi: How To Apply To School And University

4 weeks ago By DDD

How to fix KB5055518 fails to install in Windows 10?

2 weeks ago By DDD

Roblox: Dead Rails – How To Summon And Defeat Nikola Tesla

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7843

Java Tutorial

1649

CakePHP Tutorial

1403

Laravel Tutorial

1300

PHP Tutorial

1240

Related knowledge

How to set password protection for export PDF on PS Apr 06, 2025 pm 04:45 PM

Export password-protected PDF in Photoshop: Open the image file. Click "File"> "Export"> "Export as PDF". Set the "Security" option and enter the same password twice. Click "Export" to generate a PDF file.

What are the basic requirements for c language functions Apr 03, 2025 pm 10:06 PM

C language functions are the basis for code modularization and program building. They consist of declarations (function headers) and definitions (function bodies). C language uses values to pass parameters by default, but external variables can also be modified using address pass. Functions can have or have no return value, and the return value type must be consistent with the declaration. Function naming should be clear and easy to understand, using camel or underscore nomenclature. Follow the single responsibility principle and keep the function simplicity to improve maintainability and readability.

Concept of c language function Apr 03, 2025 pm 10:09 PM

C language functions are reusable code blocks. They receive input, perform operations, and return results, which modularly improves reusability and reduces complexity. The internal mechanism of the function includes parameter passing, function execution, and return values. The entire process involves optimization such as function inline. A good function is written following the principle of single responsibility, small number of parameters, naming specifications, and error handling. Pointers combined with functions can achieve more powerful functions, such as modifying external variable values. Function pointers pass functions as parameters or store addresses, and are used to implement dynamic calls to functions. Understanding function features and techniques is the key to writing efficient, maintainable, and easy to understand C programs.

The difference between H5 and mini-programs and APPs Apr 06, 2025 am 10:42 AM

H5. The main difference between mini programs and APP is: technical architecture: H5 is based on web technology, and mini programs and APP are independent applications. Experience and functions: H5 is light and easy to use, with limited functions; mini programs are lightweight and have good interactiveness; APPs are powerful and have smooth experience. Compatibility: H5 is cross-platform compatible, applets and APPs are restricted by the platform. Development cost: H5 has low development cost, medium mini programs, and highest APP. Applicable scenarios: H5 is suitable for information display, applets are suitable for lightweight applications, and APPs are suitable for complex functions.

Why do you need to call Vue.use(VueRouter) in the index.js file under the router folder? Apr 05, 2025 pm 01:03 PM

The necessity of registering VueRouter in the index.js file under the router folder When developing Vue applications, you often encounter problems with routing configuration. Special...

What are the differences and connections between c and c#? Apr 03, 2025 pm 10:36 PM

Although C and C# have similarities, they are completely different: C is a process-oriented, manual memory management, and platform-dependent language used for system programming; C# is an object-oriented, garbage collection, and platform-independent language used for desktop, web application and game development.

How to use XPath to search from a specified DOM node in JavaScript? Apr 04, 2025 pm 11:15 PM

Detailed explanation of XPath search method under DOM nodes In JavaScript, we often need to find specific nodes from the DOM tree based on XPath expressions. If you need to...

What are the different ways of promoting H5 and mini programs? Apr 06, 2025 am 11:03 AM

There are differences in the promotion methods of H5 and mini programs: platform dependence: H5 depends on the browser, and mini programs rely on specific platforms (such as WeChat). User experience: The H5 experience is poor, and the mini program provides a smooth experience similar to native applications. Communication method: H5 is spread through links, and mini programs are shared or searched through the platform. H5 promotion methods: social sharing, email marketing, QR code, SEO, paid advertising. Mini program promotion methods: platform promotion, social sharing, offline promotion, ASO, cooperation with other platforms.

See all articles