php mysql advanced selecting

table 1 = events -> holds a list of events

table 2 = response -> holds a list of users responses has a foreign key of eid which corresponds with events table

I need to join the tables together so it can return an array similar to this on php.

array(
  0 => array(
      'title' =>'',                      //title of events table
      'contents' =>'this is a demo',     //contents of开发者_运维问答 events table
      'users' => array(                  //users comes from response table
           0 = array(
             'firstname'=>'John',
           ),
           1 = array(
             'firstname'=>'James',
           )
      )
  )
);

can this be done? using mysql only? coz i know you can do it on php.

You can gather all the necessary data in MySQL with a single JOIN query.

However, PHP will not return an array like your example by default. You would have to loop over the query result set and create such an array yourself.

I'm pretty sure the answer is no, since mysql always returns a "flat" resultset. So, you can get all the results you're looking for using:

SELECT e.title, e.contents, r.firstname 
FROM events e LEFT JOIN response r ON e.id = r.eid
ORDER BY e.id, r.id

And then massaging it into the array with php, but I imagine this is what you're doing already.

EDIT:

By the way, if you want 1 row for each event, you could use GROUP_CONCAT:

SELECT e.title, e.contents, GROUP_CONCAT(DISTINCT r.firstname ORDER BY r.firstname SEPARATOR ',') as users
FROM events e LEFT JOIN response r ON e.id = r.eid
GROUP BY e.id

Just as Jason McCreary said. For you convenience, here is the query you need (though the field names might not be matching your db structure, as you did not provide this information)

SELECT 
  * 
FROM
  events
LEFT JOIN
  responses ON (events.id = responses.eid)

The SQL is:

SELECT events.id, events.title, events.contents,
       response.id AS rid, response.firstname
FROM events LEFT JOIN response
     ON events.id = response.eid

I thought I would show you how to massage the results in to the array as you wished:

$query = "SELECT events.id, events.title, events.contents, response.id AS rid, response.firstname
          FROM events LEFT JOIN response ON events.id = response.eid";

$result = mysql_query($query);

$events = array();
while ($record = mysql_fetch_assoc($result)) {
  if (!array_key_exists($record['id'], $events)) {
    $events[$record['id']] = array('title' => $record['title'], 'contents' => $record['contents'], 'users' => array());
  }

  if ($record['rid'] !== NULL) {
    $events[$record['id']]['users'][$record['rid']] = array('firstname' => $record['firstname']);
  }
}

mysql_free_result($result);