Hot File

The strength of the PDO and. MySQLi: Which Should You Use?

View: 267    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: MySQL   Fields: Other

9 point/3 review File has been tested

When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

Introduction

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.

  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

It's a cinch to connect to a database with both of these:

// 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.

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!

var_dump(PDO::getAvailableDrivers());

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:

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 dois 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.

This is another important feature that PDO has; binding parameters is considerably easier than using the numeric binding:

$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:

$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.

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.

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:

$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";
    }
}

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):

$_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).

// 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.

// 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() andmysqli_real_escape_string().

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.

The strength of the PDO and. MySQLi: Which Should You Use?

The strength of the PDO and. MySQLi: Which Should You Use? Posted on 12-01-2016  When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one? 3/10 267

Comment:

To comment you must be logged in members.

Files with category

  • SQL Query to Find Nth Highest Salary of Employee with java

    View: 196    Download: 0   Comment: 0   Author: none  

    SQL Query to Find Nth Highest Salary of Employee with java

    Category: MySQL
    Fields: Other

    0/0 review
    This is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works.

  • World OOP With PHP and MySQL

    View: 956    Download: 0   Comment: 0   Author: none  

    World OOP With PHP and MySQL

    Category: MySQL
    Fields: Other

    0.32142857142857/14 review
    Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

  • How to Build a Newsletter System With PHP and MySQL

    View: 1183    Download: 0   Comment: 0   Author: none  

    How to Build a Newsletter System With PHP and MySQL

    Category: MySQL
    Fields: Other

    1.125/4 review
    When starting any project, I like to layout the folders before I start coding, so lets do that now.

  • Top 20+ MySQL Best Practices that can not be ignored

    View: 893    Download: 0   Comment: 0   Author: none  

    Top 20+ MySQL Best Practices that can not be ignored

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    Database operations often tend to be the main bottleneck for most web applications today.

  • Create a PHP/MySQL Powered Forum from Scratch

    View: 1040    Download: 0   Comment: 0   Author: none  

    Create a PHP/MySQL Powered Forum from Scratch

    Category: MySQL
    Fields: Other

    1.5/3 review
    In this tutorial, we're going to build a PHP/MySQL powered forum from scratch.

  • Triggers Introduction to MySQL

    View: 256    Download: 0   Comment: 0   Author: none  

    Triggers Introduction to MySQL

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL.

  • How to Stored Procedures in MySQL 5

    View: 247    Download: 0   Comment: 0   Author: none  

    How to Stored Procedures in MySQL 5

    Category: MySQL
    Fields: Other

    2.25/2 review
    MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

  • The strength of the PDO and. MySQLi: Which Should You Use?

    View: 267    Download: 0   Comment: 0   Author: none  

    The strength of the PDO and. MySQLi: Which Should You Use?

    Category: MySQL
    Fields: Other

    1.5/3 review
    When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd