mysql retrieve many to many rows

Question

When I fire the following query, I get the results I require grouped by event.id:

SELECT events.id AS id,events.name AS event,categories.name AS categories
                FROM events,categories,event_dates,event_categories
                WHERE events.preview = 0 AND event_dates.start_date > DATE(NOW())
                    AND events.id = event_dates.event_id AND events.id = event_categories.event_id AND event_categories.category_id = categories.id
                GROUP BY events.id LIMIT 0,5

With the following result (event_categories being the table that links events to categories):

Array ( [id] => 144 [event] => Twickenham Stadium Tours and World Rugby Museum [name] => Sport ) 

However this is a many to many relationship and this query is only returning one category. How would rearrange this / formulate a query that would grab all categories

Answer 1

try to use group_concat in your query

    GROUP_CONCAT(DISTINCT categories.name  ORDER BY categories.name SEPARATOR ",") 
AS  categories

so the final query like :

SELECT events.id AS id,events.name AS event,
GROUP_CONCAT(DISTINCT categories.name  ORDER BY categories.name SEPARATOR ",") 
    AS  categories
                FROM events,categories,event_dates,event_categories
                WHERE events.preview = 0 AND event_dates.start_date > DATE(NOW())
                    AND events.id = event_dates.event_id AND events.id = event_categories.event_id AND event_categories.category_id = categories.id
                GROUP BY events.id LIMIT 0,5