12

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??

Chris Schmitz
  • 20,160
  • 30
  • 81
  • 137

1 Answers1

17

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 :)
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
hodl
  • 1,420
  • 12
  • 21
  • 1
    Awesome, that worked and returned an associative array, but it's only returning the first row of the query. Would I need to put it into a foreach loop to pull the rest of the rows out and insert them into a new array?? – Chris Schmitz Feb 22 '13 at 19:58
  • 3
    @ChrisSchmitz yes, just loop `while($row = $result->fetch_array(MYSQLI_ASSOC)){ var_dump($row); };` – hodl Feb 22 '13 at 23:49
  • 4
    This is only available with mysqlnd – greg Mar 10 '15 at 04:17