[thelist] Problem with SQL Query - using PHP and MySQL

Pringle, Ron RPringle at aurora-il.org
Mon Nov 13 16:19:46 CST 2006


Im converting the site I work on from classic ASP/VBscript with an Access backend to PHP5 with a MySQL 5 backend.

I have a table of events and another related table of event dates (and more tables than that, but for the sake of simlicity...). One event can take place on multiple dates over the course of any given time period.

Rather than displaying each event over and over again as a separate event on each date it occurs, I want to display the event and then the list of dates it occurs on.

What I have:

Farmers Market - 11/13
City Council Meeting - 11/15
Farmers Market - 11/20
Farmers Market - 11/27

What I want to do:

Farmers Market - 11/13, 11/20, 11/27
City Council Meeting - 11/15

I initially solved this by querying for events occuring on a specific date and then running another query within the While loop that gets all the dates for that event. This seems bad since it will run that 2nd query for each instance of the 1st query's result set. Seems like a ton of overhead.

I'm looking for a better way to return multiple dates matched to a single event in one record and loop through it for display purposes. Is this something that can only be done with two queries, or would subqueries work, or...?

Any push in the right direction would be helpful to me, as I don't even know what to Google for at this point.

Ron




More information about the thelist mailing list