Fadak - Programming - Comparison PDO & MYSQL & MYSQLI
: 2018-07-11Visitor Count : 436

Comparison PDO & MYSQL & MYSQLI


  1. PDO vs. MySQLi
  2. MYSQLI & MYSQL

PDO vs. MySQLi

PDO vs. MySQLi: Which Should You Use?

When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one? The differences, database support, stability, and performance concerns will be outlined in this article.

Summary
    PDO     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
Connection

It's a cinch to connect to a database with both of these:
1
2
3
4
5
6
7
8
    
// 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');

Please note that these connection objects / resources will be considered to exist through the rest of this tutorial.
API Support

Both PDO and MySQLi offer an object-oriented API, but MySQLi also offers a procedural API - which makes it easier for newcomers to understand. If you are familiar with the native PHP MySQL driver, you will find migration to the procedural MySQLi interface much easier. On the other hand, once you master PDO, you can use it with any database you desire!
Database Support

The core advantage of PDO over MySQLi is in its database driver support. At the time of this writing, PDO supports 12 different drivers, opposed to MySQLi, which supports MySQL only.

To print a list of all the drivers that PDO currently supports, use the following code:
1
    
var_dump(PDO::getAvailableDrivers());

What does this mean? Well, in situations when you have to switch your project to use another database, PDO makes the process transparent. So all you'll have to do is change the connection string and a few queries - if they use any methods which aren't supported by your new database. With MySQLi, you will need to rewrite every chunk of code - queries included.
Named Parameters

This is another important feature that PDO has; binding parameters is considerably easier than using the numeric binding:
1
2
3
4
5
6
7
8
9
    
$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);

...opposed to the MySQLi way:
1
2
3
4
5
6
7
8
    
$query = $mysqli->prepare('
    SELECT * FROM users
    WHERE username = ?
    AND email = ?
    AND last_login > ?');
     
$query->bind_param('sss', 'test', $mail, time() - 3600);
$query->execute();

The question mark parameter binding might seem shorter, but it isn't nearly as flexible as named parameters, due to the fact that the developer must always keep track of the parameter order; it feels "hacky" in some circumstances.

Unfortunately, MySQLi doesn't support named parameters.
Object Mapping

Both PDO and MySQLi can map results to objects. This comes in handy if you don't want to use a custom database abstraction layer, but still want ORM-like behavior. Let's imagine that we have a User class with some properties, which match field names from a database.
01
02
03
04
05
06
07
08
09
10
    
class User {
    public $id;
    public $first_name;
    public $last_name;
     
    public function info()
    {
        return '#'.$this->id.': '.$this->first_name.' '.$this->last_name;
    }
}

Without object mapping, we would need to fill each field's value (either manually or through the constructor) before we can use the info() method correctly.

This allows us to predefine these properties before the object is even constructed! For isntance:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
    
$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 way
if ($result = mysqli_query($mysqli, $query)) {
    while ($user = mysqli_fetch_object($result, 'User')) {
        echo $user->info()."\n";
    }
}
// MySQLi, object oriented way
if ($result = $mysqli->query($query)) {
    while ($user = $result->fetch_object('User')) {
        echo $user->info()."\n";
    }
}

Security

    Both libraries provide SQL injection security, as long as the developer uses them the way they were intended (read: escaping / parameter binding with prepared statements).

Lets say a hacker is trying to inject some malicious SQL through the 'username' HTTP query parameter (GET):
1
    
$_GET['username'] = "'; DELETE FROM users; /*"

If we fail to escape this, it will be included in the query "as is" - deleting all rows from the users table (both PDO and mysqli support multiple queries).
1
2
3
4
5
6
7
8
9
    
// 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'");

As you can see, PDO::quote() not only escapes the string, but it also quotes it. On the other side, mysqli_real_escape_string() will only escape the string; you will need to apply the quotes manually.
1
2
3
4
5
6
7
8
    
// 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();

    I recommend that you always use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().

Advertisement
Performance

While both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks - ~2.5% for non-prepared statements, and ~6.5% for prepared ones. Still, the native MySQL extension is even faster than both of these. So if you truly need to squeeze every last bit of performance, that is one thing you might consider.
Summary

Ultimately, PDO wins this battle with ease. With support for twelve different database drivers (eighteen different databases!) and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used (read: prepared statements).

    So if you're still working with MySQLi, maybe it's time for a change!

 

MYSQLI & MYSQL

I have been using the procedural approach with mysql* until recently. Now I want to shift to mysqli and object oriented approach. Many online resources and books state that OOP is better than procedural even in case of PHP. After going through some online tutorials, I have written a small program that connects to the database and selects a value. I want to know why the object oriented approach is better? Plus is this the right way to code an OO php web page?

 10
down vote
accepted
    

The main reason is that PHP is moving steadily in the direction of OO programming.

There's nothing wrong with using mysqli_xxx() functions rather than the OOP equivalents; it is exactly the same as far as the code is concerned.

The only issue is that you'll be getting yourself further and further behind the curve in terms of what people think of as well-written PHP code.

It's worth noting that the PDO library, which is considered the ideal for most DB code in PHP is OOP-only. It doesn't have a procedural interface. And nor do most of the other new features added to PHP in the last few versions. If you want to use PHP to its fullest, you need to know OOP anyway.

There's also the point about the ability to create an extension class for your DB -- something like this:

class myDB extends mysqli {
     .... your own stuff here to extend and improve the base mysqli class
}

Of course you can achieve the same thing with procedural code, but it's not as neat as the OOP way. And of course that's only relevant if you actually want to extend the class.

However, as a first step, just moving from mysql_xxx() to mysqli_xxx() is a great start. Moving the whole way to using the OOP interface would be even better, but just switching to the mysqli functions is a good start.

Using the procedural interface to begin with will certainly make the transition away from the old mysql_xx() functions easier, so if switching to the OOP interface is too much of a leap at the beginning, don't feel you have to do it all in one go. Start with a conversion to the procedural mysqli functions, then switch to the OOP methods later on; neither jump will be that big on its own.

 

Comparison of MySQL API options for PHP
  PHP's mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP's MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes - preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most

No

 

 

http://php.net/manual/en/mysqli.overview.php

 

 

 

 

<?php
// mysqli
$mysqli = new mysqli("example.com""user""password""database");
$result $mysqli->query("SELECT 'Привет, дорогой пользователь MySQL!' AS _message FROM DUAL");
$row $result->fetch_assoc();
echo 
htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=example.com;dbname=database''user''password');
$statement $pdo->query("SELECT 'Привет, дорогой пользователь MySQL!' AS _message FROM DUAL");
$row $statement->fetch(PDO::FETCH_ASSOC);
echo 
htmlentities($row['_message']);

// mysql
$c mysql_connect("example.com""user""password");
mysql_select_db("database");
$result mysql_query("SELECT 'Привет, дорогой пользователь MySQL!' AS _message FROM DUAL");
$row mysql_fetch_assoc($result);
echo 
htmlentities($row['_message']);
?>

   

2000-2016 CMS Fadak. ||| Version : 4.2-b1 ||| This page was produced in : 0.001 Seconds