Why mysqli prepared statemens are so hard to use?

1. WTF #1 Binding unknown number of parameters
• WTF #1.1 Calling a method with call_user_func_array()
• WTF 1.2 Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given
• The saving grace
2. WTF #2 Binding unknown number of results
3. Comments

When tying to use mysqli prepared statements for the first time, a PHP programmer utters not one, not two but whole lot of WTFs in the process, each time asking himself why he is still messing with this extension instead of using simple and consistent PDO. But folks who are not that familiar with mysqli still think it's pretty plausible and familiar extension. Let's see what this API is about for real.

This article could be also used as a reference for those stalwart developers who will decide to keep making love with a cactus.

WTF #1 Binding unknown number of parameters

When moving from sandbox textbook examples a little further, PHP programmers face with necessity to bind variables not by hand, but by means of an automated process. Let's take, for example, such a common thing as a generic function that executes an arbitrary SQL, accepting a query with placeholders and parameters to bind as arguments. For this purpose we need a method to bind a predefined array to a prepared statement instead of listing variables explicitly.

Say, we have a query and parameters like this

$sql = "INSERT INTO user (email, password) VALUES (?, ?)";
$data = array($username, $password_hash);

and need a function that will allow us to prepare this query, bind parameters and execute the statement. Unlike PDO, which can handle this task easily with its magnificent ability of sending an array of parameters right into execute, making the code as simple as

function query($sql, $params = array()) {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
}

mysqli doesn't have such a functionality at all. WTF?

All right, a typical prepared query in mysqli looks like this

$stmt = $pdo->prepare($sql);
$stmt->execute("ss", $username, $password_hash);
$stmt->execute();

To make this code work with arbitrary parameters we need a tool to call bind_param with arbitrary number of variables. Inevitably, we have to resort to a third-party tool like call_user_func_array() function which is doing exactly what we need - calling a function using an array provided as a source for its arguments. Unfortunately, this function is not that easy to use itself, as at fist we have to figure out how to call it with a method.

WTF #1.1 Calling a method with call_user_func_array()

It is not that clear from the manual page, but there is a way to call this function not only with a standalone function but with an object's method as well. For this we heed to supply the first parameter in the form as array consists of two elements - an object variable and the method name:

call_user_func_array(array($stmt, "bind_param"), $data);

Well done?... Alas, we'll get an error. WTF?

WTF 1.2 Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given

So we learn a harsh truth here: one cannot just take their variables, put them into array and send into call_user_func_array()**

All right, finally we were able to conjure something like this:

The saving grace

Luckily, since version 5.6 PHP supports enormously useful "argument unpacking" (or "three dot") operator that that does exactly the same as call_user_func_array() but much more elegant:

$stmt = $db->prepare($sql);
$types = str_repeat('s', count($data));
$statement->bind_param($types, ...$data);
#                               ^ notice the dots?
$statement->execute();

But nevertheless it takes two times more code than PDO and isn't much portable, as it cannot be run on PHP versions less than 5.6 that are still in use.

WTF #2 Binding unknown number of results

You won't believe it, but one cannot fetch a regular associative array from a prepared statement in mysqli. There is no such method in mysqli_statement class! Before recent, it was a real pain to get just a simple associative array from a prepared statement in mysqli. Luckily, a workaround was implemented that lets you to get a mysqli_result object from a prepared statement and to fetch an array from it.