php: what is the fastest way to display related data between three db tables

Question

Hi,

I'm looking for the best way to display data from 3 tables. Let me explain. I have one master table that lists events each event can have a number of categories so I'm using a join table linking the categories to the events. I want to loop through all the events and display under each event the categories that have been related via the join table and I'm not sure what is the most efficient way of doing so?

more info:

I can easily do this by adding in an SQL statement placed inside the events loop which pulls out the selected categories by updating the event id in the statement each time it loops, but I'm sure there is a more elegant method which uses one sql query which creates an array that can then be used within the events loop - I'm just not sure where i should start looking?

Answer 1

Assuming that you have tables like the following:

create table event ( 
   event_id int not null primary key,
   ....
);

create table evcat ( 
  event_id int not null,
   cat_id int not null,
   key (event_id, cat_id )
);

create table category (
   cat_id int not null,
   ...
);

Then the query is simply a join across the 3 tables:

select * from event ev
  join evcat ec on ec.event_id = ev.event_id
  join category c on c.cat_id= ec.cat_id;