PHP mysqli - return an associative array from a prepared statement

Question

I'm trying to use mysqli to prepare a statement in order to safely pass in variable values to the query. All of that is working for me, but the problem I'm running into is getting the result in an associative array. Here's my structure so far:

$query = $c->stmt_init();
$query->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);
if ($result = $query->execute()){
    $a  = $result->fetch_array(MYSQLI_ASSOC); // this doesn't work :/
} else{
    error_log ("Didn't work");
}

As you can see, I have a lot of columns getting passed back so I'd like to not have to bind them each to a variable.

On top of that, the end goal is to pass back a json encoded associative array to the rest of my application.

I've looked up the issue in the php documentation and on stack exchange and I've found suggestions, but I can't seem to get them to work. Could anyone lend a hand??

Answer 1

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