Join and/or Array Question ...

[brainardp]

Here's my code:

<?php

include("dbinfo.inc.php");

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = mysql_query("SELECT wa04Location FROM wabox0404")  or die(mysql_error()); 

while($row = mysql_fetch_array($query))
{
$location[] = $row['wa04Location'];
} 
$location = array_unique($location);

$i = 0; 
while(array_key_exists($i,$location)) 
{ 
echo($location[$i]."<br><br>"); 
$query2 = mysql_query("SELECT * FROM wabox0404 WHERE wa04Location = '$location[$i]' ORDER BY wa04Date ASC") or die(mysql_error()); 

while($row2 = mysql_fetch_array($query2)) 
{ 
echo($row2['wa04Date']."<br>...other details<br><br>"); 
} 
$i++; 
} 

?>

It's giving me the first location and doing everything I want it to, but when it jumps to the second location ... it stops after printing the location name. ?????

I've got two tables in a MySql database ... one that has location information:

LocID
LocName
LocAddress
LocCity
LocState
LocZip
LocPhone
LocRegion

And one that has tournament information:

TourID
TourLocation
TourDate
TourEvent
TourPlayers
TourEntry
TourAdded
TourTotal
TourDirector
Tour1st
Tour1stMoney
Tour2nd
Tour2ndMoney
Tour3rd
Tour3rdMoney

I want to be able to list tournament results by location, by date ... like this:

Location

Date
Event
Players
Entry
Added
Total
etc.

Date
Event
Players
Entry
Added
Total
etc.

Date
Event
Players
Entry
Added
Total
etc.

Location

Date
Event
Players
Entry
Added
Total
etc.

Location

Date
Event
Players
Entry
Added
Total
etc.

Date
Event
Players
Entry
Added
Total
etc.

Is is less complex to work from the one table or do a join to get location name, then list by date the tournaments/results with PHP. Please advise. Thanks.

[bastien]

have you tried a for loop instead? Also note the movement of the location item to outside the quotes in the sql string

$i = 0;  

for $i=0;$i<count($location);$i++;)
{  

  echo($location[$i]."<br><br>");  

  $query2 = mysql_query("SELECT * FROM wabox0404 WHERE   wa04Location = '".$location[$i]."' ORDER BY wa04Date ASC") or die(mysql_error());  

  while($row2 = mysql_fetch_array($query2))  

  {  

     echo($row2['wa04Date']."<br>...other details<br><br>");  

   }  

}