How would you do this......

[jesseainskeep]

Event Table
ID............Name.........................Address
1...............Shopping Trip............123 4th Street
2...............Kids Day...................222 5th Ave.
3...............Music Day.................233 7th Ave.

eventDates Table
ID........eventId.............startDate
1...........1......................2007-05-26
2...........1......................2007-05-28
3...........2......................2007-04-25
4...........3......................2007-04-25
4...........3......................2007-05-25

Here is a quick representation of two tables I have. eventId is the foreign key to id in the Events table. There is a one to many relationship here. The problem comes in PHP when I want to display all the events.

Here is what I want the table to look like on the web page.

..........Name...................Dates
edit........Shopping Trip........5-26-07, 5-28-07
edit........Kids Day...............4-25-07
edit........Music Day.............4-25-07, 5-25-07

I think this is pretty standard for a back-end admin. The problem is getting the information pulled out.

If I use PHP, and do a query and LEFT JOIN the two tables, I will actually get a two records for Shopping Trip. One with each different day. I can put this information in a holding array, and cycle through until I get to a new ID and just keeping adding dates to as an array.This is one way I've done it.

The second way would be to just search the events table. Do a loop to display the information, while in the loop I can do a search for each individual id on the eventDates table. So in this instance I would do 4 different queries instead of one.

What would be a different approach to doing this? I'm open to new suggestions.

If this doesn't make sense, let me know where so I can clarify a little.

[bubbisthedog]

I totally agree with the PHP loop method in this situation.

[jesseainskeep]

I totally agree with the PHP loop method in this situation.

Your talking about the 2nd way, right?

This is what I do most of the time. The only problem is say 40 events show up. That means you are doing 40 different queries. I'm just curious on how much you are taxing database, and if this could be done differently.

[bubbisthedog]

Actually, no, I do it the using the first method. Sorry I wasn't clear on that. I'd use SQL to get the results, and then for each result that is fetched do with it what I need done (using PHP) and then move on to the next fetched result. Personally, I like to get as much done as possible with one SQL statement as possible, and then use minimal logic (and minimal additional SQL statements, if necessary) to do the rest. I even use SQL to handle logic at times (using CASE statement logic) so that I don't have to do it in PHP. That's just how I usually do things, and my results generally fair pretty well.