bind_result into an array PHP mysqli prepared statement

Question

wondering how i could bind the results of a PHP prepared statement into an array and then how i could go about calling them. for example this query

$q = $DBH->prepare("SELECT * FROM users WHERE username = ?");
$q->bind_param("s", $user);
$q->execute();

and this would return the results the username, email, and id. wondering if i could bind it in an array, and then store it in a variable so i could call it throughout the page?

Answer 1

PHP 5.3 introduced mysqli_stmt::get_result, which returns a resultset object. You can then call mysqli_result::fetch_array() or mysqli_result::fetch_assoc(). It's only available with the native MySQL driver, though.

Answer 2

Rule of thumb is that when you have more than one column in the result then you should use get_result() and fetch_array() or fetch_all(). These are the functions designed to fetch the results as arrays.

The purpose of bind_result() was to bind each column separately. Each column should be bound to one variable reference. Granted it's not very useful, but it might come in handy in rare cases. Some older versions of PHP didn't even have get_result().

If you can't use get_result() and you still want to fetch multiple columns into an array, then you need to do something to dereference the values. This means giving them a new zval container. The only way I can think of doing this is to use a loop.

$data = [];
$q->bind_result($data["category_name"], $data["id"]);
while ($q->fetch()) {
    $row = [];
    foreach ($data as $key => $val) {
        $row[$key] = $val;
    }
    $array[] = $row;
}

Another solution as mentioned in the comments in PHP manual is to use array_map, which internally will do the same, but in one line using an anonymous function.

while ($q->fetch()) {
    $array[] = array_map(fn($a) => $a , $data);
}

Both solutions above will have the same effect as the following:

$q = $DBH->prepare("SELECT * FROM users WHERE username = ?");
$q->bind_param("s", $user);
$q->execute();
$result = $q->get_result();
$array = $result->fetch_all(MYSQLI_ASSOC);