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

Phil Turmel pturmel-webdev at turmel.org
Mon Nov 13 18:34:46 CST 2006


Pringle, Ron wrote:
> 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
> 

Ron,

I concur with Matt that running the extra queries is often a good
solution, particularly with the memory-hungry PHP-MySQL interface.  It
this case, though, I'd take advantage of 'GROUP_CONCAT' in MySQL's
repertoire:

Select EventTitle, Min(EventDate) As FirstDate,
   Group_Concat(DateFormat(EventDate, '%c/%e')
   Separator ', ') As EventDateString
 From Events Inner Join EventDates Using (EventID)
Where EventDate>Current_Date
Group By EventTitle
Order By Min(EventDate);

This knocks out your inner loop, unless there's other information inside
each event date's record that you also need.  It also gets the result
ordered by the first event date for each event.

HTH,

Phil




More information about the thelist mailing list