How to use mySQL database entries as variables in a form drop down list

[chrisidas]

Hi,

 

I was wondering, is it possible for me to select entries from my database, to use in a dropdown list in a form.

 

The form is for a sports website. In the database, i have all the players and the teams of which they play for.

 

I would like for the drop down list to just show players from a certain team using php (e.g. on the 'Manchester United' page, i would like it so only players associated with 'Manchester United' in my database show up.

 

The table in my database is called 'players' and the colums are called 'teamname' and 'playername'.

 

Is this possible? If so, what would the code look like?

 

Thanks,

 

Chis

[Psycho]

I'm not really sure what you are asking. Your title states you want to create a drop-down list, but then in your description you state

on the 'Manchester United' page, i would like it so only players associated with 'Manchester United' in my database show up

which would seem to indicate you want the ability to have separate pages (i.e. not a select list for the user to choose). Both are possible. In either case the solution is similar. You just need to provide the value for the team criteria to the query.

 

However, to make this easier you should not be storing the team name in the players table. Instead you should have a teams table that has a team id and and a team name. Then the players table should use the team id. You can use the team name, but it requires more validation and manipulation of the value.

 

If you want separate pages for each team, just create one page and pass an additional parameter to the page. E.g.

<a href="showteam.php?teamid=1">Manchester United</a>

This assumes that Manchester United has the ID of 1 in the database. If you didn't use IDs (and instead just use the team name, then you would need to urlencode() the names for the query string).

 

Then in your page you would generate your query like so

$teamID = (int) $_GET['teamid']
$query = "SELECT name FROM players WHERE teamid = $teamID";

 

Or, if you use a select list, you would just set up the name/value same as the link shown above. Then you would capture the team id via POST data instead of GET data.

[HenryC]

<?php
$sql = mysql_query("SELECT teamplayer FROM tbl_name WHERE teamname = 'Man u'");

echo '<select name="players">';
while($row = mysql_fetch_array($sql)){
$player = $row['teamplayer'];
?>
<option value="<?php echo $player; ?>"><?php echo $player; ?></option>
<?php
}
?>
</select>

[chrisidas]

I'm not really sure what you are asking. Your title states you want to create a drop-down list, but then in your description you state

on the 'Manchester United' page, i would like it so only players associated with 'Manchester United' in my database show up

which would seem to indicate you want the ability to have separate pages (i.e. not a select list for the user to choose). Both are possible. In either case the solution is similar. You just need to provide the value for the team criteria to the query.

 

However, to make this easier you should not be storing the team name in the players table. Instead you should have a teams table that has a team id and and a team name. Then the players table should use the team id. You can use the team name, but it requires more validation and manipulation of the value.

 

If you want separate pages for each team, just create one page and pass an additional parameter to the page. E.g.

<a href="showteam.php?teamid=1">Manchester United</a>

This assumes that Manchester United has the ID of 1 in the database. If you didn't use IDs (and instead just use the team name, then you would need to urlencode() the names for the query string).

 

Then in your page you would generate your query like so

$teamID = (int) $_GET['teamid']
$query = "SELECT name FROM players WHERE teamid = $teamID";

 

Or, if you use a select list, you would just set up the name/value same as the link shown above. Then you would capture the team id via POST data instead of GET data.

Sorry i just meant i wanted the form on the Manchester United page.

Basically, where you usually put in what you want to show in the drop down list, i want it so it selects the options for me from the database. So, when i update the database, it updates the options in the drop down list for the form.

 

I think what HenryC said is what i'm looking for, will give that a go.

 

Cheers guys.

[chrisidas]

Got it sorted now, used HenryC's method, thanks again