Only returns the first row

[rocket]

Can any one tell me why this code only returns the first row in the the table "team" I need it to return all the rows in the table but I cant figure out why it does not?

Thanks
Tim

<?


   $SQL = "SELECT * FROM team";


# execute SQL statement
$retid = mysql_db_query($db, $SQL, $cid);

# check for errors
if (!$retid) { echo( mysql_error()); }
else {

    # display results

    while ($row = mysql_fetch_array($retid)) {

        $name = $row["name"];
		$cat = $row["cat"];
		$number = $row["number"];



echo"<b>$name</b><br>";

$SQL = "SELECT * FROM users WHERE team='$number'";


# execute SQL statement
$retid = mysql_db_query($db, $SQL, $cid);

# check for errors
if (!$retid) { echo( mysql_error()); }
else {

    # display results

    while ($row = mysql_fetch_array($retid)) {

        $firstname = $row["firstname"];


echo"$firstname";

}}
 }}
?>

[laserlight]

The problem is that you're re-using $retid in the middle of a while loop that depends on $retid.
The same goes for $row.

<?php
//assume existing database server connection in $cid
//select database, assuming $db has been declared
mysql_select_db($db);

$sql = "SELECT * FROM team";
$retid = mysql_query($sql)
	OR die(mysql_error());

while ($row = mysql_fetch_array($retid)) {
	$name = $row["name"];
	$cat = $row["cat"];
	$number = $row["number"];

echo "<b>$name</b><br>";

$sql = "SELECT * FROM users WHERE team='$number'";

$retid2 = mysql_query($sql)
	OR die(mysql_error());

while ($row2 = mysql_fetch_array($retid2)) {
	$firstname = $row2["firstname"];
	echo $firstname;
}
}
?>

You might be able to simplify to something like:

$sql = "SELECT
		team.name AS name,
		team.cat  AS cat,
		team.number AS number,
		users.firstname AS firstname
	FROM team, users
	WHERE team.number=users.team";
$retid = mysql_query($sql)
	OR die(mysql_error());
while ($row = mysql_fetch_array($retid)) {
	$name = $row["name"];
	$cat = $row["cat"];
	$number = $row["number"];
	$firstname = $row["firstname"];

echo "<b>$name</b><br>$firstname";
}

But you'll have to test and tweak, my SQL is pretty poor.

[rocket]

Thanks that will help!!

Tim