PDO versus MySQLi versus MySQL | Impact on Connect Using MySQLi and PDO


Since PHP 7 was introduced and PHP 5 finish of support existence was arrived at, many website hosts dropped PHP 5 using their servers and PHP developers were made to migrate their code to utilize PHP 7.

Among the changes of PHP 7 was the stopping from the original MySQL extension. Developers needed to decide to migrate their code to make use of either the MySQLi or and also the PDO extension.

Look at this article to discover the variations and choose the best idea that you should migrate your code to operate on PHP 7 or later, too find out about some suggested packages which use PDO or MySQLi for performing common database purposes like backup, security, user registration and login, database abstraction, ORM , etc..


1. PDO versus MySQLi versus MySQL

2. Impact on Connect Using MySQLi and PDO

3. PDO versus MySQLi versus MySQL Performance Benchmark

4. PDO versus MysQLi Security using Prepared Statements

5. Comparison between PDO and MySQLi Relation to Usage

6. Converting MySQL Applications to make use of PDO or MySQLi

7. Package Strategies for use with PDO and MySQLi

8. Things to Use within a brand new Project: PDO or MySQLi?

PDO Versus MySQLi

1. PDO versus MySQLi versus MySQL

As everyone knows, MySQL is definitely an Free Relational Database Management System (RDBMS) that utilizes Structured Query Language (SQL). MySQL is really a central element of the LAMP Free Web software stack (along with other “AMP” stacks): Apache MySQL and PHP.

MySQL is a valuable part of just about every Free PHP application. Illustrations for PHP and MySQL based scripts are phpBB, osCommerce and Joomla.

PHP accustomed to include the initial MySQL extension built-by which supports with older MySQL versions. However, this extension was deprecated in support of MySQLi (i for improved). Simultaneously PHP ongoing to evolve and also the PDO (PHP Data Objects) extension was introduced to become common interface for being able to access various kinds of database.

MySQLi is yet another 1 of 3 methods to access a MySQL database server. Such as the MySQL extension, the brand new MySQLi is built to take better benefit of newer MySQL server features.

The PHP Data Objects (PDO) extension defines a light-weight, common interface for being able to access databases in PHP. Each database driver that’s based on PDO interface can expose database specific features, too common functions.

PDO supplies a data access abstraction layer, meaning, no matter which database type you utilize, exactly the same functions are for sale to perform queries and fetch results. PDO doesn’t give a full database abstraction. i.e. it doesn’t rewrite SQL queries or emulate missing features. You need to use a complete-blown abstraction layer package if you want that capacity.

2. Impact on Connect Using MySQLi and PDO

Either PDO and MySQLi present an Object Oriented interface towards the extension functions but MySQLi also provides a procedural API, that makes it simpler for newcomers to know. If you’re acquainted with the initial PHP MySQL extension, you’ll find migration towards the procedural MySQLi interface simpler. Here is a good example:

// 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’)

The primary benefit of PDO over MySQLi is incorporated in the database support. PDO supports 12 different database types, towards MySQLi, which assists MySQL only.

If you need to switch any project to make use of another database, PDO helps make the process simpler. So all you need to do is alter the connection string and also at most a couple of queries when they use any syntax which isn’t based on your brand-new database.

3. PDO versus MysQLi Security using Prepared Statements

Both PDO and MySQLi provide support for prepared queries. This can help stopping SQL injection security issues, as lengthy while you just use prepared queries to insert dynamic parameters within the queries.

For instance, think about a hacker that attempts to inject malicious SQL passing a forged value towards the parameter code of the HTTP Publish request that may be emulated such as this:

$_Publish[‘code’] = “‘ DELETE FROM products /*”

If you don’t escape this value, it will likely be placed in query out of the box, so it would delete all rows in the products table.

One method to make queries safer staying away from SQL injection would be to quote string parameter values to insert escape figures.

// PDO, “manual” getting away

$name = PDO::quote( $_Publish[‘code’] )

$pdo->query( “SELECT id, name FROM products WHERE code = $code” )

// mysqli, “manual” getting away

$name = mysqli_real_escape_string( $_Publish[‘code’] )

$mysqli->query( “SELECT id, name FROM products WHERE name = ‘$code'” )

PDO::quote() not just escapes the string, it adds quotes. mysqli_real_escape_string() is only going to escape the string, so you will have to add some quotes by hand.

// PDO, prepared statement

$pdo->prepare( ‘SELECT id, name FROM products WHERE code = :code’ )

$pdo->execute( array( ‘:code’ => $_Publish[‘code’] ) )

// mysqli, prepared statements

$query = $mysqli->prepare(‘SELECT id, name FROM users WHERE code = ?’)

$query->bind_param(‘s’, $_Publish[‘code’])


PDO will also support client side queries. Which means that if this prepares a question, it doesn’t have to talk with the server.

Since MySQLi uses native prepared statements, it’ll may really be faster to make use of mysqli_real_escape_string rather of utilizing prepared statements, even though it is still a safe and secure solution.

4. PDO versus MySQLi versus MySQL Performance Benchmark

There have been some PHP MySQL performance benchmark tests in the past by Jonathan Robson too by Radu Potop. Despite the fact that these tests were performed with PHP 5.3 and nowadays we’re using PHP 7 or later, let us think about these results as reference.

Essentially they reveal that for SELECT queries using prepared statements MySQLi runs a little faster. Still it might not be significant based on your purposes.

Bear in mind that PDO automatically uses client side prepared statements emulation. When utilizing native prepared statements, there’s yet another round-trip towards the server to organize the statement, therefore the overall query execution time might be really more than when utilizing native prepared statements for managing a query just once.

As pointed out above, you should use mysqli_real_escape_string function to quote dynamic parameters as if you would do while using the original MySQL extension. This really is like emulating prepared queries the actual way it is performed with PDO because you wouldn’t have to perform yet another round-trip towards the server to organize the statement.

5. Comparison between PDO and MySQLi Relation to Usage

While MySQLi has its own advantages being able to access MySQL server features, PDO sometimes has an advantage and can be a better fit for that user, based on requirements and convenience. Like the majority of things, the choice that actually works best depends upon that you ask and just what situation you’ll need MySQLi or PDO for.

MySQLi is only for MySQL databases, whereas PDO is flexible capable to use multiple database systems, including IBM, Oracle and MySQL. Should you ever need to switch databases or give a database independent solution, using MySQLi directly isn’t the smartest choice. You might still use MySQLi in database independent solutions utilizing an abstraction layer.

Binding parameters with MySQLi isn’t as easy or flexible just like PDO. MySQLi utilizes a numbered parameter system and does no’t support named parameters like PDO. MySQLi has good support and maintenance, making the transition to and employ the brand new system safe and sound.

You’ll be able to make use of additional features obtainable in the newer versions of MySQL servers. This is among the greatest benefits of MySQLi. PDO might not have extensive support to make the most of MySQL’ newer abilities.

Cellular phone process with MySQLi isn’t just easy, but is automatic once the PHP 5 MySQL extension which comes built-in Home windows or Linux distributions.

6. Converting MySQL Applications to make use of PDO or MySQLi

For those who have a task while using old MySQL extension and you have to convert it to make use of MySQLi or PDO, it might take a while to rewrite it should you be not utilizing a database abstraction layer.

Should you prefer a quick a easy solution that doesn’t require to alter much your code, you can test either the package PDO or even the MySQLi to make use of all these extensions through the way of MySQL wrapper functions that decision mysql_* functions using PDO by Aziz S. Hussain or PHP MySQL to MySQLi by Dave Cruz for PDO and MySQLi extension functions correspondingly.

This will help you to help make your code operate on PHP 7 when you perform a real migration of code that doesn’t need a wrapper.

Dave Cruz also offers written a tutorial regarding how to migrate mysql code to mysqli thinking about the variations between individuals extensions.

7. Package Strategies for use with PDO and MySQLi

There are lots of packages which you can use for varied purposes to gain access to MySQL using either MySQLi or PDO.

Here are a a few of the newer PHP packages that offer safe and effective wrappers for PDO several purposes.

PDO Backup and Synchronize Database

PHP Multi MySQLDump (Alessandro Quintiliani)

Dump MySQL database tables for file download

PHP Backup to WebDav Server (Dmitry Mamontov)

Backup files along with a database to some WebDav server

PHP Database Synchronize Tables (Ettore Moretti)

Synchronize tables between two database using PDO

8. Things to Use within a brand new Project: PDO or MySQLi?

While PDO might be slightly slower and never make the most of MySQL server features, it might be better to write less database dependent code while getting more readable code using named parameters for prepared queries. So either option might be good based on that which you value.

This short article attempted to provide some qualifying criterion you should think about when deciding which is right for you.

Should you loved this short article recommend it with other developers while using like and share buttons in this article. For those who have questions or comments, publish a remark below.

5 1 vote
Article Rating
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x