dynamic form dropdown list from mysql db

[sws]

Hi folks,

I'm trying to create a form where users can select players for a fantasy sports team from a dropdown list. The drop down list must contain all of the players from my player table in the database.  Any idea how to do that ?

This is the criteria. Entrants must select 3 LW's, 3 C's, 3 RW's, 4 D, and 2 G's.

I intend to have a drop down menu for each player they must select. Dropdown menu's must pull all of the players from my player table.

Also, once they pick one LW, that players name should not show up on the other two dropdowns for LW's.

Can anybody help me out ?

Thanks in advance.

[sws]

I have re-worded this. I don't think I was precise enough the first time around.

[switchdoc]

Hello,

  Assuming you are using MySQL, this is how I do this: (I am taking while guesses that you have player names and auto-incremented player IDs. If you don't, you might want to consider it. )

[code]
<select name='players'>
<?php
$query = "SELECT * from players";
$result = mysql_query($query) OR DIE ("There was an error .mysql_error());
while ($line = mysql_fetch_array($result,MYSQL_ASSOC)){
$playerid = $line["playerid"];
$player = $line["player"];

echo "<option value='$playerid'>$player</option>\n";

}
?>

</select>


[/code]

The first part queries all the players out of the database.
The second part assigns the variables in the array to straight variable names which are easier to work with.

The while loop runs through each player that is pulled and writes the <option> line for each player.

Make sense?

Hope this helps,

Switch

[sws]

Yup, that makes total sense.  So I've got an html form and I am going to have several of these drop down boxes based on player position. By the way, I am using mysql and I do have unique player id's.

Your code looks perfect for what I am looking to do.

Now, the only other thing is do i put that php code,(obviously tayloring the sql query to only select where player_position = whatever), in the <td> cell of my table where the dropdown is supposed to go ?

And also, when they click submit, I have to code in the button that the values are to be plugged into the database. Is this correct ?

[sws]

I've used this code supplied and tailored it to my needs.

It's working but my drop down list containt $player  over and over again.

Can someone please tell me what I've done wrong ?

[code]<select name='players'>
<?
$lw1_sql = "Select * FROM nhl_players WHERE position = 'LW' ORDER BY name ASC";
$lw1_query = mysql_query($lw1_sql) OR DIE ("There was an error" .mysql_error());
while ($table_row = mysql_fetch_array($lw1_query,MYSQL_ASSOC)){
$player_id = $table_row['id'];
$player = $table_row['name'];

echo '<option value="$player_id">$player</option>n';

}
?>
</select>[/code]

[Monkeymatt]

Your quotes are wrong. This line:
[code]
echo '<option value="$player_id">$player</option>n';
[/code]
needs to have double quotes or variables concatted for it to insert variable names and not what is literally there:
[code]
echo "<option value='$player_id'>$player</option>\n
[/code]
or
[code]
echo '<option value="'.$player_id.'">'.$player.'</option>\n';
[/code]

Monkeymatt

[sws]

Thanks VERY much Matt.  Worked like a charm.  Dam quotes. I always have trouble knowing what quotes to use where.

I used the first example that you gave me.

Just out of curiosity, what does the concatenator do in the second statement ?

[switchdoc]

Its kinda like a way to write this + this + this to allow you to move in and out of the quotes.

In that case you are basically saying:

echo '<option value="'.$player_id.'">

plus

'.$player.' (the variable not the actual words $player)

plus

</option>\n';

The '.'s link it together.

Matt may be able to explain it better but thats the basic jist anyway.

-Switch

[switchdoc]

A better example is found in the query:

$lw1_query = mysql_query($lw1_sql) OR DIE ("There was an error" .mysql_error());


("There was an error" .mysql_error());

Is basically saying if there is an error to output:

"There was an error" + whatever mysql_error()) reports as the error.

So you would get "There was an error unspecified field 'monkey' in table 'zoo'" or whatever

-Switch

[sws]

Ahhh I see. Yeah I know how the concatenation works. I just didn't read matt's post properly. Makes sense to me now.

Thanks for all your help guys.

[exoskeleton]

guys thank you for this post, i also need this code...

more power...