How can I put the results of a MySQLi prepared statement into an associative array?

Question

I have a sql query and a mysqli prepared statement:

$sql = 'SELECT photographers.photographer_id, photographers.photographer_name
    FROM photographers';

$stmt = $conn->stmt_init(); 
if ($stmt->prepare($sql)) { 
    $stmt->bind_result($photographer_id, $photographer_name);  
    $OK = $stmt->execute(); 
    $stmt->fetch();
}

How can I store the results in an associative array so I can loop it later and get to all the data returned by the sql string?

Answer 1

Oddly enough, you can't. There's simply no way to get a mysqli_result object from a mysqli_stmt instance. I've always considered this a major flaw, and would guess that this is one of the major reasons that mysqli never reached any real popularity. These days it's been pretty much superseded by PDO, which does what you want with out effort.

Edit: My answer only means that you can't do it by default. Of course you can implement it yourself, like Chris suggested. Still, I think you should use PDO instead, if it's at all possible.

Answer 2

Try the following:

$meta = $statement->result_metadata(); 

while ($field = $meta->fetch_field()) { 
    $params[] = &$row[$field->name]; 
} 

call_user_func_array(array($statement, 'bind_result'), $params);      
while ($statement->fetch()) { 
    foreach($row as $key => $val) { 
     $c[$key] = $val; 
    } 
    $hits[] = $c; 
} 
$statement->close();

First you get the query metadata and from that obtain all the fields you've fetched (you could do this manually, but this code works for all queries rather than building by hand). The call_user_fund_array functions calls the bind_result function for you on each of those parameters.

After that it is just a matter of running through each row and creating an associative array for each row and adding that to an array resulting in all the results.