PDO vs. MySQLi: With PHP Database APIs

Introduction

The mysql_ extension is no longer supported as of PHP 7, and its methods have been deprecated since PHP 5.5. Unfortunately, there are still a tonne of outdated tutorials on the internet that newbies will simply copy and paste and use on a shared hosting platform with an outdated PHP version, perpetuating its legacy.

You can select either MySQLi or PDO if your PHP application uses MySQL or MariaDB. The latter is an abstraction layer that enables you to use a unified API for all 12 database drivers it supports, whilst the former is only an enhanced version with procedural and OOP support and added prepared statements.

However, it should be noted that MySQL is unquestionably the most widely used database in the PHP community.

Theoretically, the conversation ought to be finished. Since using a single database type is so much easier, we don't require vendor-specific APIs for each form of database that exists. While there is undoubtedly much truth in this, PDO MYSQL just lacks the most recent and cutting-edge functionality that MySQLi does. Since it would utterly negate any incentive to use the vendor-specific API, I'm not sure why this is the case. Having said that, I anticipate that most individuals won't require these extra functions, but a small percentage undoubtedly will.

Here's a link for anyone who's interested in full writeup of PDO and MySQLi.

PDO Advantages

There are several areas where MySQLi must catch up to PDO. It should include characteristics like:

Useful fetch modes

Permit passing items and variables directly into the execute

Ability to automatically discover variable types (When submitted to the server, everything is really processed as a string before being transformed to the correct type. With native prepared statements, this operates flawlessly, but in some edge circumstances, such as when LIKE is used in emulation mode, it does not.)

Gives prepared statements a choice to automatically buffer outcomes

Named variables (though useless with emulation mode turned off in PDO, as you can only use the same name once)

As you can see, MySQLi should take note of quite a few things from PDO if it wants to stay relevant.

If it works, there should be absolutely no change. People always claim that learning a new extension would be difficult, but in reality, they are already largely interchangeable. I'll be utilising native prepared statements for this article, although emulated ones are also appropriate. Here is a brief explanation of how the two vary.

MySQLi Advantages

Despite being significantly less crucial for most users, PDO also lacks some functionality, such as:

Asynchronous queries     

Possibility of updating a row with the same values (this is possible in PDO as a function Object() { [native code] } setting, but you can't alter it later) and getting more information on the impacted rows

Proper database closing method

Multiple queries at once (though it can if emulation mode is turned on in PDO)

Automatic cleanup with persistent connections

Which should I use?

In my view, PDO ought to be used by default, especially for newcomers, because of its adaptability, overall predictability, and practical fetch modes. For sophisticated users who require the newest, MySQL-specific features, MySQLi would be a better option.

Ironically, while more inexperienced PHP developers tend to utilise MySQLi, they tend to believe that PDO is always the sole alternative that is completely appropriate. This is completely absurd on every level. Of course, most developers don't actually require the additional advanced functionality offered by MySQLi, but as was already noted, some may find them to be of great use.

It's particularly odd that new users are reluctant to convert to PDO while many experienced users continue to tout PDO's benefits of being simple to transition from database drivers. Anyone who thinks it's simple to transition between databases effortlessly in PDO has obviously never tried it. Since each driver is unique, switching from Microsoft SQL Server to MySQL won't be automated. First things first: the syntax is quite close, practically identical. I'll give examples to illustrate this. Furthermore, PDO is a PDO MYSQL extension rather than some abstraction layer over MySQLi.

Code Difference

As previously mentioned, there are only very minor syntactical differences between PDO and MySQLi. MySQLi utilises camelCase but PDO adheres to the outdated PHP snake case pattern. Furthermore, PDO employs the conventional syntax for functions, whereas MySQLi uses methods as object attributes.

|   $arr = $mysqli->query("SELECT * FROM myTable")->fetch_all(MYSQLI_ASSOC);
|   $arr = $pdo->query("SELECT * FROM myTable")->fetchAll(PDO::FETCH_ASSOC);

In actuality, using a wrapper, query builder, or ORM is the best option. You might appreciate the rather simple MySQLi wrapper I have. Despite the fact that PDO allows you to directly bind values to execute, it is still not desirable. You can link all of your calls in the class I created while providing the values to bind as a parameter argument. See what you are capable of.

| $arr = $mysqli->query("SELECT * FROM myTable WHERE id > ?", [12])->fetchAll('assoc');

You now have an entire associative array stored in the variable in a far more concise manner. It's strange why both PDO and MySQLi don't do it like this.

For the rest of the tutorial, we'll be using prepared statements, as there isn't a good reason not to use them for SQL injection protection, unless you're using a feature like async, which currently doesn't support them. Otherwise, you'll need to properly manually format your queries.

Creating a New Database Connection

PDO

$dsn = "mysql:host=localhost;dbname=myDatabase;charset=utf8mb4";
$options = [
  PDO::ATTR_EMULATE_PREPARES   => false, // turn off emulation mode for "real" prepared statements
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
];
try {
  $pdo = new PDO($dsn, "username", "password", $options);
} catch (Exception $e) {
  error_log($e->getMessage());
  exit('Something weird happened'); //something a user can understand
}

MySQLi

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $mysqli = new mysqli("localhost", "username", "password", "databaseName");
  $mysqli->set_charset("utf8mb4");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
}

Insert, Update, Delete

PDO

$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->execute([$_POST['name'], 29]);
$stmt = null;
Copy

MySQLi

$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->bind_param("si", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close();
Copy

It should be noted that with PDO, you can chain prepare() and execute(), though you won't be to get the affected rows, so I can't see that being useful.

Get Number of Affected Rows

PDO

$stmt->rowCount();
Copy

MySQLi

$stmt->affected_rows;
Copy

Get Latest Primary Key Inserted

Notice, how both of these use the connection variable, instead of $stmt.

PDO

$pdo->lastInsertId();
Copy

MySQLi

$mysqli->insert_id;
Copy

Get Rows Matched

PDO

In PDO, the only way to achieve this is by setting it as a connection option to change the behavior of rowCount(), unfortunately. This means rowCount() will either return rows matched or rows changed for your entire database connection, but not both.

$options = [
  PDO::MYSQL_ATTR_FOUND_ROWS => true
];
Copy

MySQLi

$mysqli->info;
Copy

This will output an entire string of information, like so:

Rows matched: 1 Changed: 0 Warnings: 0

I have no idea why they thought this would be a wise implementation, as it would be far more convenient in an array. Luckily, you can do this.

preg_match_all('/(\S[^:]+): (\d+)/', $mysqli->info, $matches); 
$infoArr = array_combine ($matches[1], $matches[2]);
var_export($infoArr);
Copy

Now you can access the values pretty easily. Note, that the value is a string, so you either cast all the values to ints, so === can work or strictly check with ==.

['Rows matched' => '1', 'Changed' => '0', 'Warnings' => '0']
Copy

Fetching

Fetch Associative Array

PDO

$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id <= ?");
$stmt->execute([5]);
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Fetch Single Row

PDO

$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->execute([$_POST['name']]);
$arr = $stmt->fetch(PDO::FETCH_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_assoc();
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Fetch Single Value (Scalar)

PDO

$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->execute([$_POST['name']]);
$arr = $stmt->fetch(PDO::FETCH_COLUMN);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_row()[0];
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Fetch Array of Objects

PDO

class myClass {}
$stmt = $pdo->prepare("SELECT name, age, weight FROM myTable WHERE name = ?");
$stmt->execute(['Joe']);
$arr = $stmt->fetchAll(PDO::FETCH_CLASS, 'myClass');
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

class myClass {}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

PDO really shines here as you can see. It's very odd why MySQLi doesn't have something like $mysqli_result->fetch_all(MYSQLI_OBJ). PDO even takes it a step further and has an awesome way to deal with the annoying default behavior of it getting called after the class constructor, via bitwising it with fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'myClass'). It's possible to replicate this behavior in MySQLi, but it relies on either leaving out the constructor and relying on the magic __set() or by only setting it in the constructor if it doesn't equal the default value.

Like

PDO

$search = "%{$_POST['search']}%";
$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?");
$stmt->execute([$search]);
$arr = $stmt->fetchAll();
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$search = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); 
$stmt->bind_param("s", $search);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Fetch Modes

This is by far my favorite feature about PDO. The fetch modes in PDO are extremely useful and it's quite shocking that MySQLi hasn't added them yet.

Fetch Key/Value Pair

PDO

$stmt = $pdo->prepare("SELECT event_name, location FROM events WHERE id < ?");
$stmt->execute([25]);
$arr = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$arr = [];
$id = 25;
$stmt = $con->prepare("SELECT event_name, location FROM events WHERE id < ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_row()) {
  $arr[$row[0]] = $row[1];
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Output:

['Cool Event' => 'Seattle', 'Fun Event' => 'Dallas', 'Boring Event' => 'Chicago']
Copy

Fetch Group Column

PDO

$stmt = $pdo->prepare("SELECT hair_color, name FROM myTable WHERE id < ?");
$stmt->execute([10]);
$arr = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$arr = [];
$id = 10;
$stmt = $con->prepare("SELECT hair_color, name FROM myTable WHERE id < ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_row()) {
  $arr[$row[0]][] = $row[1];
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Output:

[
  'blonde' => ['Patrick', 'Olivia'],
  'brunette' => ['Kyle', 'Ricky'],
  'red' => ['Jordan', 'Eric']
]
Copy

Fetch Key/Value Pair Array

PDO

$stmt = $pdo->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?");
$stmt->execute([200]);
$arr = $stmt->fetchAll(PDO::FETCH_UNIQUE);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$arr = [];
$weight = 200;
$stmt = $con->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?");
$stmt->bind_param("i", $weight);
$stmt->execute();
$result = $stmt->get_result();
$firstColName = $result->fetch_field_direct(0)->name;
while($row = $stmtResult->fetch_assoc()) {
  $firstColVal = $row[$firstColName];
  unset($row[$firstColName]);
  $arr[$firstColVal] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Output:

[
  17 => ['max_bench' => 230, 'max_squat' => 175],
  84 => ['max_bench' => 195, 'max_squat' => 235],
  136 => ['max_bench' => 135, 'max_squat' => 285]
]
Copy

Fetch Group

PDO

$stmt = $pdo->prepare("SELECT hair_color, name, age FROM myTable WHERE id < ?");
$stmt->execute([12]);
$arr = $stmt->fetchAll(PDO::FETCH_GROUP);
if(!$arr) exit('No rows');
var_export($arr);
$stmt = null;
Copy

MySQLi

$arr = [];
$id = 12;
$stmt = $con->prepare("SELECT hair_color, name, age FROM myTable WHERE id < ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
$firstColName = $result->fetch_field_direct(0)->name;
while($row = $stmtResult->fetch_assoc()) {
  $firstColVal = $row[$firstColName];
  unset($row[$firstColName]);
  $arr[$firstColVal][] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
Copy

Output:

[
  'blonde' => [
    ['name' => 'Patrick', 'age' => 22],
    ['name' => 'Olivia', 'age' => 18]
  ],
  'brunette'  => [
    ['name' => 'Kyle', 'age'=> 25],
    ['name' => 'Ricky', 'age' => 34]
  ],
   'red'  => [
    ['name' => 'Jordan', 'age' => 17],
    ['name' => 'Eric', 'age' => 52]
  ]
]
Copy

Where In Array

PDO

$inArr = [1, 3, 5];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause)");
$stmt->execute($inArr);
$resArr = $stmt->fetchAll();
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt = null;
Copy

MySQLi

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)");
$stmt->bind_param($types, ...$inArr);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
Copy

Where In Array With Other Placeholders

PDO

$inArr = [1, 3, 5];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause) AND id < ?");
$fullArr = array_merge($inArr, [5]); //merge WHERE IN array with other value(s)
$stmt->execute($fullArr);
$resArr = $stmt->fetchAll();
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt = null;
Copy

MySQLi

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$types .= 'i'; //add 1 more int type
$fullArr = array_merge($inArr, [26]); //merge WHERE IN array with other value(s)
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?");
$stmt->bind_param($types, ...$fullArr); //4 placeholders to bind
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
Copy

Transactions

PDO

try {
  $pdo->beginTransaction();
  $stmt1 = $pdo->prepare("INSERT INTO myTable (name, state) VALUES (?, ?)");
  $stmt2 = $pdo->prepare("UPDATE myTable SET age = ? WHERE id = ?");
  if(!$stmt1->execute(['Rick', 'NY'])) throw new Exception('Stmt 1 Failed');
  else if(!$stmt2->execute([27, 139])) throw new Exception('Stmt 2 Failed');
  $stmt1 = null;
  $stmt2 = null;
  $pdo->commit();
} catch(Exception $e) {
  $pdo->rollback();
  throw $e;
}
Copy

You might be wondering why I'm solely checking for truthiness on execute() with PDO. This is due to the fact that it can return false, while silently failing. Here's a more detailed explanation.

MySQLi

try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
  $stmt1->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt2->bind_param("si", $_POST['name'], $_SESSION['id']);
  $stmt1->execute();
  $stmt2->execute();
  $stmt1->close();
  $stmt2->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  throw $e;
}
Copy

MySQLi also has a gotcha, but the solution is to convert errors to exception with a global handler. Read about it more here.

Named Parameters

This is a PDO-only feature, but it's only useful if emulation mode is turned off. Otherwise, you can only use the same variable once. It should also be noted that the leading colon is not required, but this isn't documented anywhere. It likely will eventually anyway, but you never know, I suppose. You also can't mix ? placeholders with named ones; it's either one or the other.

$stmt = $pdo->prepare("UPDATE myTable SET name = :name WHERE id = :id");
$stmt->execute([':name' => 'David', ':id' => 3]);
$stmt = null;
Copy