While Looping Help

[ponypony]

First off, I will try and describe what I want done..
I have multiple data entries (shows) that are all set at one, and I want them all selected, from a different table it gets data based off the shows ID, and lists them out.
However I am only able to get it to list out all the shows, but it only lists the data from the last show listed.. Not the two above it.
Heres my code.

<?php
include ('connect.php');
$query = "SELECT showname, id, date FROM shows WHERE date = '1'";
$result = mysql_query($query)
or die ("No shows to run!");

while ($pet = mysql_fetch_object($result))
{
$showname = $pet->showname;
$date = $pet->date;
$id = $pet->id;
$sid = id; 

echo "
<table border='1' cellpadding='3' width='400'>
<td colspan='6'>$showname</td>
<tr>
<td>ID $id</td>
<td>Entrant</td>
<td>Show Name</td>
<tr>";


$query = "SELECT petname, petid, showid, showname FROM `show` WHERE showid = '$id'";
}
$result = mysql_query($query)
or die ("No shows to run!");
while ($pet = mysql_fetch_object($result))
{
$petname = $pet->petname;
$petid = $pet->petid;
$showid = $pet->showid;
$showname = $pet->showname;

echo "
<td>$showid</td>
<td>$petname</td>
<td>$showname</td>
<tr>
";
}
?>


</tr></td></table><p>

 

[misson]

If you'd indented your code, you'd notice there are two while loops at the same level, rather than nested. You want to nest them, which means you shouldn't assign the result of the second query to $result.

Also, your HTML is malformed. You need to close table rows (<tr> elements) and you start a table with every iteration of the 1st while loop but close only 1 table, outside the while loop. You should balance every operation. The thing that starts a table should also finish it.

You can use the complex syntax (not actually complex to use) to interpolate variables into strings so you don't need as many temporary variables (eg "<td>{$pet->showname}</td>").

"die()" is a terrible way to handle errors.

In the second loop, $showid won't change for a given show. Did you mean $petid?

The names of the two tables, "shows" and "show", are too similar. Are either of "contestants" or "entrants" a more descriptive name for "show"?

Does shows.showname hold the same information as show.showname? If so, your tables are subject to update anomalies. In DB lingo, you don't want a column to be functionally dependent on a foreign key. In layman's terms, this roughly translates as the only information that should appear in multiple tables are IDs. If you don't have a separate "pet" table, your DB is subject to insert and delete anomalies. If you do have a "pet" table, "show" is also subject to update anomalies.

Here's another version of your code, one that prints a table for each entry in table "shows". If you want a single table, make the appropriate alterations.

<?php
include ('connect.php');
$query = "SELECT showname, id, date FROM shows WHERE date = '1'";
if ($result = mysql_query($query)) {
	while ($show = mysql_fetch_object($result)) {
		echo "<table border='1' cellpadding='3' width='400'>
  <caption>{$show->showname}</caption>
  <thead>
    <tr>
      <th>ID {$show->id}</th><th>Entrant</th><th>Show Name</th>
    </tr>
  </thead><tbody>";

		$query = "SELECT petname, petid, showname FROM `show` WHERE showid = '{$show->id}'";
		if ($pets = mysql_query($query)) {
			while ($pet = mysql_fetch_object($pets)) {
				echo "    <tr><td>{$pet->petid}</td><td>{$pet->petname}</td><td>{$pet->showname}</td></tr>";
			}
		} else {
			echo "    <tr><td colspan='3'>No shows</tr>";
		}
		echo "  </tbody>\n</table>";
	}
} else {
	echo '<p>No shows.</p>';
}
?>