PHP When and why do I need to call store_result()?

Question

In the PHP manual I found

You must call mysqli_stmt_store_result() for every query that successfully produces a result set (SELECT, SHOW, DESCRIBE, EXPLAIN), if and only if you want to buffer the complete result set by the client, so that the subsequent mysqli_stmt_fetch() call returns buffered data.

I do not understand this explanation at all. In particular, I have no idea what is meant with with buffer the complete result set by the client.

---

For example, if I wanted to get the amount of num_rows of a select query, I usually did this:

$result = $mysqli->query("SELECT name FROM users WHERE age ='3' "));
echo $result->num_rows;

If I use now prepared statments, then

$sql = 'SELECT name FROM users WHERE age = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', 3);
$stmt->execute();
// $stmt->store_result();
$numrows = $stmt->num_rows;

returns always 0, except if I uncomment // $stmt->store_result();. I though prepare+bind+execute should be the same as the old query command, but apparently its not. What is the store_result method doing? Why do I need it here?

Answer 1

The reason why you have to call store_result() is because prepared statements in mysqli by default operate in unbuffered mode.

This is contrary to the information presented in the docs. You have to keep in mind that mysqli was developed as a shim rather than a proper DB library. MySQLi is full of bugs and suffers from a lot of bad decisions. One of them is unbuffered mode for prepared statements.

In fact you do not have to use store_result() at all. This function is not very useful, because you still need to bind the results to variables and fetch each row individually. A much better option is get_result().

$age = 3;
$sql = 'SELECT name FROM users WHERE age = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $age);
$stmt->execute();
$result = $stmt->get_result();

This is 4 lines compared to query() method, but the end result is the same. $result contains an instance of mysqli_result class.

You could write your own wrapper for it to make your life simpler. Here is an example how you could turn it into a single line with a wrapper function.

function mysqli_prepared_query(mysqli $mysqli, string $sql, array $params): mysqli_result
{
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    $stmt->execute();
    return $stmt->get_result();
}

$result =  mysqli_prepared_query($mysqli, 'SELECT ?,?', [1, 2]);

Of course the best solution would be to switch to PDO or use some kind of abstraction library, so that you do not have to deal with these confusing methods.