Pulling Lists from MySQL

[WendyLady]

I hope this is a simple question that I've just been sitting here too long writing code & my brain is fried.

What I am basically trying to do is pull lists from MySql.  I have three tables.  One table is a list of possible dates, and the other two tables have various events, each one with a date that corresponds to one in the first table.

What I want to be able to do is pull each date, then list below each one all the rows from the 2nd & 3rd tables that have that date.  There may be multiple dates, each one with multiple listings below it.  For some reason, I can't get my brain around how to echo this out logically.

The logic flows as:
--find all dates in table 1 that have boolean flag of "1"
--go to table 2 and table 3 and find all events that have one of these dates
--list each date with its corresponding events below

I'm not looking for explicit code, but rather just some help with how to do this in code.  It seems like it should be simple, so it is making me feel really dumb, LOL.  Am I thinking about it too hard?

Thanks!

Wendy

[tomfmason]

I find that I also tend to over think things. Any ways I think that this is what you are asking for.

Here is an example for getting the data from the first table with the where clause of 1
[code=php:0]
$sql= mysql_query("SELECT * FROM `table1` WHERE `something` = '1'") or die(mysql_error());
while ($row = mysql_fetch_assoc($sql)) {
         $date = $row['date'];//or what ever row you want from table one
         $q = mysql_query("SELECT `event` FROM `table2`, `table3` WHERE `date` = '$date'") or die(mysql_error());
         while($rw = mysql_fetch_assoc($q)) {
                 $event = $rw['event'];
                  echo "$date<br />";
                  echo "$event<br />";
         }
}
[/code]

Hope this helps.
Tom

[switchdoc]

I believe what you need here is a JOIN statement.

Here are a few pages on it. See if this helps:

http://mysql.com/doc/refman/5.0/en/join.html
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

[switchdoc]

Looks like Tom beat me too it, and better too!

[ToonMariner]

BE CAREFUL of the script presented by Tom...

Many hosts limit you to 50 queries per script.Switch doc was correct in that a join could be used... But the script above would fail if 50 records were returned from teh first query (on MOST hosting packages - unless you have extended rights and can remove any restrictiosn on your site.)

The alternative is loading ALL the contents of each table into an array (one array for each table) and then using hash tables to grab the correct results (no where near as pretty as the queries IMO but won't fail like that scritp could)

[WendyLady]

Sorry for long delay before adding update:

I didn't get any of these to work, and finally decided to add a secondary step.  The user first sees a list of dates, then chooses one & a second query pulls everything for that date.  It is slightly more convoluted for the user, but it works well.

Thanks everyone!