Why would this only display one record?

NZ_Kiwi

I have 4 records in the BD but only one shows? any ideas why?

<select size="1" name="team1_id<?php echo $i ?>">
<?php
$result3 = mysql_query ("SELECT * From results_comp_run_teams where comp_run_id = $comp_run_id");
$row3 = mysql_fetch_array($result3);

$team_id = $row3["team_id"];

$result4 = mysql_query ("SELECT * from results_team where team_id = $team_id");
while($row4 = mysql_fetch_array($result4))
{
?>

<option value="<?php echo $row4["team_id"]; ?>"><?php echo $row4["team_name"]; ?></option>

<?php
};
?>
</select>

sidney

might be the semi colon at the end of your while loop

NZ_Kiwi

Originally posted by sidney
might be the semi colon at the end of your while loop

Many thanks, I've just tried this but no it didn't help... any other ideas?

planetsim

Well check your database how many times does the team_id of the one being used appear?

[edit]
It may also have something to do with not looping the first query however as I am not sure what is suppose to be happening im probably wrong
[/edit]

NZ_Kiwi

Originally posted by planetsim
Well check your database how many times does the team_id of the one being used appear?

[edit]
It may also have something to do with not looping the first query however as I am not sure what is suppose to be happening im probably wrong
[/edit]

These are my DB....
results_team has four entries.... so does results_comp_run_teams...

CREATE TABLE results_team (
team_id int(10) NOT NULL auto_increment,
team_name varchar(200) NOT NULL default '',
parent_club varchar(150) NOT NULL default '',
team_logo varchar(250) NOT NULL default '',
KEY team_id (team_id)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

CREATE TABLE results_comp_run_teams (
comp_run_id int(10) NOT NULL default '0',
team_id int(10) NOT NULL default '0'
) TYPE=MyISAM;

bodzan

...all you can get is one row from the results_team...

To say it like this in the first query (row3) you get only one row in result because you don't loop it...
Then in the next query you ask for all the data that has the team_id from row3...

The way it should work is like if you have 4 records in the results_comp_run_teams table with this team_id you will get four records if not, well, there is no way with this code to do it:

If I'm guessing right try this code:

<select size="1" name="team1_id<?php echo $i ?>">
<?php
$result3 = mysql_query ("SELECT * From results_comp_run_teams where comp_run_id = $comp_run_id");
while($row3 = mysql_fetch_array($result3)){//loop this query to get all the team_id's from this table

$team_id = $row3["team_id"];

$result4 = mysql_query ("SELECT * from results_team where team_id = $team_id");
while($row4 = mysql_fetch_array($result4))
{
?>

<option value="<?php echo $row4["team_id"]; ?>"><?php echo $row4["team_name"]; ?></option>

<?php
}
}
?>
</select>