CODEWITHSUNDEEP
phparraysmysqliprepared-statement
mcbeav
mcbeav

Reputation: 12275

bind_result into an array PHP mysqli prepared statement

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?

Upvotes: 16

Views: 36007

Answers (3)

Dharman
Dharman

Reputation: 33238

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);

Upvotes: 5

mti2935
mti2935

Reputation: 12027

See Call to undefined method mysqli_stmt::get_result for an example of how to use bind_result() instead of get_result() to loop through a result set and store the values from each row in a numerically-indexed array.

Upvotes: 0

Ian Dunn
Ian Dunn

Reputation: 3680

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.

Upvotes: 14

Related Questions