looping a mysql query with php

[homer15]

hey everyone,

i'm still pretty new to using sql with php and i need some help.

what i need is to have some html tables built automatically off of two mysql tables.

mysqltable_1 includes dates and some basic header information for each date
mysqltable_2 has events that occurred on each date, multiple events for each date

i want the html table to contain the header information for each date, and the rows beneath it to list the event, and i want it to keep building tables until the first mysqltable_1 no longer has any new dates.
example:

Date 1
event 1	event 1 info
event 2	event 2 info

Date 2
event 3	event 3 info
event 4	event 4 info

what i thought might work follows (replace the ^ with ><'s):

^?php $host = "localhost"; $db = "local_events"; mysql_connect($host, $user, $pass) or die (mysql_error()); mysql_select_db($db) or die (mysql_error()); // Querying the table $query = mysql_query("SELECT * FROM `mysqltable_1`"); while($row=mysql_fetch_array($query)) { $DateVal=$row['Date']; ?^ ^table^ ^tr^^td^ ^?echo $row['Date']; ?^ ^/td^^/tr^ ^?php // Querying the table $query = mysql_query("SELECT * FROM `mysqltable_2` where `Date`=$DateVal"); while($row=mysql_fetch_array($query)) { ?^ ^tr^ ^td^ ^?echo $row['Event']; ?^ ^/td^^td^ ^?echo $row['EventInfo']; ?^ ^/td^ ^/tr^ ^? } ?^ ^/table^ ^/td^ ^/tr^ ^/table^ ^? } ?^

this builds only one table, but it builds it perfectly. now, i need it to repeat for every date that is in mysqltable_1. i know this probably isn't the most effecient (and obviously not the correct) way to do this. can anyway help me along to get to where i want?

[aym]

If I understand your post correctly, what you want to do can be achieved by joining the two tables, something like:

SELECT * FROM mysqltable1, mysqltable2 WHERE mysqltable1.date = mysqltable2.date

More info on joins:
http://dev.mysql.com/doc/mysql/en/join.html

I may be able to help more if you post the structure of the tables.

HTH

EDIT:
BTW you can use [ php ] [/ php ] tags to post PHP code without having to replace < and >.