How do I output a multidimensional array from multiple relational MySQL tables?

Question

I have three tables: Event, Events and Users. The Event table stores information relevant to a specific event (name, date, etc), the Users table stores user information (name, age, etc) and the Events table is the relational between the two, storing only Event_ID and User_ID.

I need to list out all the events for a specific user (lets say User_ID = 20), and show all the other users who are also in that event below each.

For example, lets say the user is going to "Steve's Birthday", "Homecoming Party" and "Art Show Downtown". I'd need to display:

Steve's Birthday - Also going: Sarah, Mike, Tom Homecoming Party - Also going: Mike, Andy, Roger, Steve Art Show Downtown - Also going: Sarah

I've tried querying the database first to find all Event_ID's in the Events table that match the User_ID. Then I take that array, and for each find the event_name and event_date. But I'm confusing myself way too much trying to figure out how to then find each person also going to that event.

Can anyone understand what I'm trying to do and suggest a method to pull it off?

Answer 1

You can use a subselect in an in clause and something like this:

SELECT `event`.*, `user`.* from `user`,`user_event`, `event` 
WHERE `event`.`id` in (
   SELECT `user_event`.`event_id` from `user_event`, `user` 
   WHERE `user`.`id` = ?
)
AND `user`.`id` = `user_event`.`user_id`
AND `user_event`.`event_id` = `event`.`id`
ORDER BY `event`.`id`

Which basically reads: find all users going to the following events - the list of events can be found by taking the event_ids of the events that joe is going to.

In the subselect I don't pull in the event table because I get enough information (the event_id) from my many to many table.

There is going to be redundant data in the rows. You're going to want to post process the resulting array by iterating over the rows.

$groupedArray = array();
foreach ($result as $item)
{
   if(!isset($groupedArray[$item['event_id']])
   {
       // Create an empty array if we don't have one for this grouping yet.
       $groupedArray[$item['event_id']] = array();
   }

   // Append the current item to the subarray.    
   $groupedArray[$item['event_id']][] = $item;

}

This kind of loop is actually pretty idiomatic. You might want to write/find a general "regroup" function.

Answer 2

Something like

SELECT 
  e.Name,
  Concat_WS(', ', u.Name) AS AlsoGoing
  /* maybe some more stuff */
FROM
  Events eu
LEFT JOIN Events a
  ON (eu.Event_ID = a.Event_ID)
LEFT JOIN Users u
  ON u.User_ID = a.User_ID
LEFT JOIN Event e
  ON (eu.Event_ID = e.Event_ID)
WHERE
  eu.User_ID = 20 /* or some other ID */
  AND a.User_ID <> eu.User_ID
GROUP BY
  eu.Event_ID;     

The first join creates the relationship "who else is going to the same event". The where-clause filters out identical users (if the list is made for Steven, he won't appear in "who else is going"), and only selects the events Steven's attending. The second and third join simply add the user and event info to the ID's. Grouping by the event an using the (separated) makes sure that you get one row per event (with "people also coming summed up").