Usability problems of mysqli compared to PDO

Disclaimer: By no means I am going to say that mysqli is worse than PDO. Mysqli is an excellent extension, with many specific features. But it's just not intended to be used directly. To make it usable, one have to always wrap it into a helper library, to reduce the enormous amount of code that otherwise have to be written by hand. I myself have such a wrapper library, SafeMysql, which I am very happy with (and which in many aspects is better than vanilla PDO).

But for the average PHP/MySQL user, standard APIs are the only known methods for database interaction. Thus they tend to use both extensions right in the application code, without any intermediate wrapper around. For such a use PDO is an indisputable winner, and I'll show you why.

Note that this article is written out of premise that a query should be consisted of constant values only. Means all query parts should be hardcoded in your script. To do so, a query with placeholders have to be prepared first, and then executed with variables sent to db separately. If your views are so ancient that you don't take it as a cast-iron rule, here is an article for you, The Hitchhiker's Guide to SQL Injection prevention. After getting convinced, you may proceed further:

Named placeholders

To me it's rather a yet another source of confusion (there are thousands questions on Stack Overflow, caused by mere typos in placeholder names), which also makes your code bloated. But many people consider it the main usability advantage of PDO over mysqli. After all, PDO users have a choice:

$stmt = $pdo->prepare("UPDATE users SET name=:name, email=:email WHERE id=:id");

or

$stmt = $pdo->prepare("UPDATE users SET name=?, email=? WHERE id=?)");

while mysqli users have to stick with the latter.

General inconvenience in binding

PDO has a magnificent feature of sending variables right into execute():

$stmt = $pdo->prepare("INSERT INTO numbers VALUES (?)");$stmt->execute([$num]);

or even more concise, utilizing the method chaining:

$pdo->prepare("INSERT INTO numbers VALUES (?)")->execute([$num]);

Mysqli Doesn't have such a feature. You have to always bind by hand:

$stmt = $mysqli->prepare("INSERT INTO numbers VALUES (?)");$stmt->bind_param("i", $num);$stmt->execute();

Difference: 1.5x ro 3x.

Getting single column value

PDO has a dedicated function, PDOStatement::fetchColumn

$stmt = $pdo->prepare("SELECT value FROM params WHERE name = ?");$stmt->execute([$name]);$value = $stmt->fetchColumn();

Mysqli. Doesn't have any. You have to write by hand

$stmt = $mysqli->prepare("SELECT value FROM params WHERE name = ?");$stmt->bind_param("s", $name);$stmt->bind_result($value);$stmt->execute();$stmt->fetch();

Note the unnatural way of getting a value: with PDO we assign a variable, which is readable and unambiguous, while with mysqli it's counter-intuitive. Although with Mysqli you may use assignment as well, there are some pitfalls which we will discuss later.

Difference: 1.5x.

Getting multiple rows

Here comes the only helper function available in mysqli. Thus, in getting just a nested array consists of all the rows returned, these two APIs are equal.

Both PDO and mysqli can make it in a single line (if no variables are involved):

$data = $pdo->query("SELECT * FROM table")->fetchALL();$data = $mysqli->query("SELECT * FROM table")->fetch_all();

But there are other result formats.

PDO can return a query result in many different formats, such as list, dictionary, array, indexed nested array, grouped data, all thanks to PDO::FETCH_* constants. So, for example, to get 1-dimensional array out of the query we need not a single line of extra code:

$stmt = $pdo->prepare("SELECT id FROM table WHERE val > ?");$stmt->execute([$val]);$ids = $stmt->fetchALL(PDO::FETCH_COLUMN);

Mysqli, having only blunt fetch_all(), can't help us at all. To get the same array we have to iterate by hand:

$stmt = $mysqli->prepare("SELECT id FROM table WHERE val > ?");$stmt->bind_param("i", $val);$stmt->execute();$res = $stmt->get_result();$ids = [];
while ($row = $res->fetch_row())
    $ids[] = $row[0];
}

If you want to group your results, it's even worse:

PDO keeps with concise code as usual:

$data = $pdo->query("SELECT date, id, price FROM table")->fetchAll(PDO::FETCH_GROUP);

Mysqli asks you to write a program again:

$res = $mysqli->query("SELECT date, id, price FROM table");$results = array();
while ($row = $res->fetch_assoc()) {
    if (!isset($results[$row['date']])) {
        $results[$row['date']] = array();
    }
    $results[$row['date']][] = $row;
}

Difference: 0x to 8x.

Binding unknown number of parameters

Imagine we need to run a query with array to be sent into IN or VALUES clause. So, first of all we need to create a query with as many placeholders as many values are in our array:

$arr = array(1,2,3);$in  = str_repeat('?,', count($arr) - 1) . '?';$stmt = $db->prepare("SELECT * FROM table WHERE column IN ($in)");

Up to this point this code is the same for PDO and mysqli. But the rest...

PDO:

$stmt->execute($arr);$data = $stmt->fetchAll();

Nuff said.

Mysqli, due to its extremely inconvenient binding that requires a reference, will make us to write a whole program again:

$bind = array();
foreach($params as $key => $val){
    $bind[$key] = &$params[$key];
}$types = str_repeat("s", count($params));array_unshift($bind, $types);call_user_func_array(array($stmt, 'bind_param'), $bind);$stmt->execute();$res = $stm->get_result();$data = $res->fetch_all();

Difference: 5x.

PHP >= 5.6 edit

With splat operator the situation is getting back to sanity, making it at least usable, but still asks us to write quite a bunch of code:

$stmt = $db->prepare($select);$types = str_repeat('s', count($arr));$statement->bind_param($types, ...$arr);$statement->execute();$res = $statement->get_result();$data = $res->fetch_all();

Difference: 3x.

Compatibility

You have to realize that in the examples above the most efficient mysqli code were used, which was made possible thanks to mysqlnd driver. But if this driver is unavailable, the situation changes dramatically.

Although I wouldn't call this problem an important one, as older versions are slowly fading away, there is still a usual problem of legacy code and open source libraries. And although we can limit a PHP version in Composer, for mysqlnd, according to my knowledge, there is no way to set such a requirement.

Two functions that are unavailable if mysqli is based on old libmysql, not mysqlnd:

• fetch_all()
• get_result()

And if fetch_all() is just a syntax sugar (though a lame one compared to PDO), the lack of get_result() makes it PAIN when it comes to getting data out of the query. There will be no way to get a familiar array out of the prepared statement (yes, it's as weird as it sounds). You have to stick to bind_result() with all its inconveniences: all field names have to be listed explicitly up to four times. Thus, to get a simple row you will have to write something like

Mysqlnd-based mysqli lets you use familiar fetch_assoc() method without hassle

$stmt = $mysqli->prepare("SELECT id, name, price, amount FROM table WHERE id = ?");$stmt->bind_param("i", $vid);$stmt->execute();$res = $stmt->get_result();$row = $res->fetch_assoc();

Libmysql-based mysqli asks you to list every variable explicitly as well as setting array keys manually:

$stmt = $mysqli->prepare("SELECT id, name, price, amount FROM table WHERE id = ?");$stmt->bind_param("i", $id);$stmt->bind_result($id, $name, $price, $amount);$stmt->execute();$row = array(
    'id' => $id;
    'name' => $name;
    'price' => $price;
    'amount' => $amount;
);

Difference: 1,5x to unlimited, depends on the number of variables returned.

And yet again compare both to PDO:

$stmt = $pdo->prepare("SELECT id, name, price, amount FROM table WHERE id = ?");$stmt->execute([$id]);$row = $stmt->fetch();

Of course, all the inconveniences above could be overcame by a good wrapper. This is why if you choose mysqli, you definitely have to use one.