Two tables

[onthespot]

I just cant get my head around this.

I have two tables, fixtures and competitions.

I have a page where it is displaying the competitions.

 

$leagues = mysql_query("SELECT `comp_name`, `game`, `format`
FROM `competitions`
WHERE `comp_type` = 'league'");
echo"<table cellspacing=\"10\">
<tr>
<td>Competition</td>
<td>Game</td>
<td>Format</td>
</tr>";

while( $row = mysql_fetch_assoc($leagues)) {
extract($row);
$info = explode("_",$row[comp_name]);


echo"
<tr>
<td><A HREF='leaguefix.php?comp=$comp_name'>$info[2]</A></td>
<td>$info[1]</td>
<td>$info[0]</td>
</tr>";
}
echo"</table>";
echo mysql_error(); 

 

However, once all the fixtures for that league have been played, the name isn't disappearing from the list. To do this I thought about checking the fixtures table for any remaining fixtures and somehow if there is displaying that comp name otherwise not displaying it.

 

The table structures

 

fixtures

id comp home_user away_user

 

competitions

id comp

 

Note that both comp in fixtures and comp_name in competitions refer to the save value.

I really can't understand how to check for fixtures against competitions.

Thanks

[onthespot]

Just thought about this, and realised that if i use the fixtures table to create the list of competitions that currently have fixtures to be played in.

However, how would I then call additional information from the competitions table only if needed (there were fixtures left to play in the fixtures table)

[onthespot]

   <?php
$competition = ("SELECT comp FROM competitions WHERE comp_type = 'league' ");
$user = $_SESSION['username'];
$comp = mysql_query("SELECT '$competition'
FROM `fixtures`
WHERE `home_user` = '$user' OR `away_user` = '$user' ");
$num_rows = mysql_num_rows($comp);

if ($num_rows > 0){

$leagues = mysql_query("SELECT `comp_name`, `game`, `format`
FROM `competitions`
WHERE `comp_type` = 'league'
AND `comp_name` = '$competition'");
echo"<table cellspacing=\"10\">
<tr>
<td>Competition</td>
<td>Game</td>
<td>Format</td>
</tr>";

while( $row = mysql_fetch_assoc($leagues)) {
extract($row);
$info = explode("_",$row[comp_name]);


echo"
<tr>
<td><A HREF='leaguefix.php?comp=$comp_name'>$info[2]</A></td>
<td>$info[1]</td>
<td>$info[0]</td>
</tr>";
}
echo"</table>";
echo mysql_error(); 
}
else 
{}
?>

 

Thats what I have come up with.

I have taken the name of the comp from competitions, checked if that comp has any rows in fixtures and if it does, perform the code.

Getting an error though?

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource