How to Fetch All in Assoc Array from a Prepared Statement

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

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_func_array() function calls the mysqli_stmt::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.

PHP mysqli - return an associative array from a prepared statement

If you have the MySql Native Driver extension (mysqlnd), you can use the get_result method to obtain a ResultSet, and then fetch from it the usual way:

$query = $c->prepare("SELECT e._id,e.description,e.eventDate,e.eventTime,e.address,e.locationDescription,i.guestId,r.guestId IS NOT NULL AS 'RSVP-ed'  FROM eventList AS e  JOIN inviteList AS i ON e._id = i.eventId LEFT JOIN rsvpList AS r ON r.eventId = e._id AND i.guestId = r.guestId JOIN guestList AS g ON g._id = i.guestId WHERE g.groupName = ?");
$query->bind_param('s',$groupName);
$query->execute();
$result = $query->get_result();
$a  = $result->fetch_array(MYSQLI_ASSOC); // this does work :)

Get ASSOC array with PDO without using prepared statement

the statement needs to be executed ...

$stmt = $db->query("SELECT * from tracks WHERE online = 1");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

passing it as second argument should also work:

$stmt = $db->query("SELECT * from tracks WHERE online = 1", PDO::FETCH_ASSOC);
$data = $stmt->fetchAll();

or as one-liner:

$data = $db->query("SELECT * from tracks WHERE online = 1")->fetchAll(PDO::FETCH_ASSOC);

there's also:

$stmt->setFetchMode(PDO::FETCH_ASSOC);

---

Related Topics