在每个循环上创建下拉菜单，将数据库值分配给选项元素

Im working on a page for a sports team where the coach can select his team. What im trying to do is:

1) Print different positions

2) Assign next to position name, players who are ONLY relevant to the position. (i.e if the positions name is flanker only flankers should be displayed in the drop-down menu)

My logic for the above problem is:

Assign position names to array called $position Loop over array
querying database, with position having a different value after each loop.
Selected players that match the position gets assigned to an array called $playerName
Print the $position variable
Create dropdown menu for each position
assign value from $playername array to the option element in the drop down menu.

Now there should be different positions with dropdown menus, next to them, containing player names relevant to position.

//create position array

$position = array(
    "THP",
    "HKR",
    "LH",
    "LK4",
    "LK5",
    "FLH"
);
echo '<form name="slectPlayers" method="post">';
foreach ($position as $pposition) {
    $result = mysql_query("SELECT `player_id`,`name`,`surname` FROM `player_info` WHERE `position` = '$pposition'") or die(mysql_error());
    while ($row = mysql_fetch_array($result)) { //create arrays
        $id[]             = $row['player_id'];
        $playerName[]     = $row['name'];
        $playerLastName[] = $row['surname'];
        // print position and open select element
        foreach ($position as $playerPosition) {
            print $playerPosition;
            echo '<select>';
            foreach ($playerName as $name) { //assign playername to position element
                echo '<option>' . $name;
                '</option>';
                echo '</select>';
                echo '<br />';
            } //close assign player nae to position loop
        } //end print position and open select loop
    } //end while  loop
} //end opening for each loop
echo '</form>';

unfortunately for me either my logic is wrong or my code is incorrect. This is the ouput I get: (Note only the name Tendai is displayed in all dropdown meus no other names appear)

enter image description here

Ive been struggling with this one all morning if someone could point me in the right direction it would be greatly appreciated

(note to modirators the picure above does not contain real names and is only a fictional database)

This could be your problem

foreach ($position as $pposition) {
    $result = mysql_query("SELECT `player_id`,`name`,`surname` FROM `player_info` WHERE `position` = '$pposition'") or die(mysql_error());
    while ($row = mysql_fetch_array($result)) { //create arrays
        $id[]             = $row['player_id'];
        $playerName[]     = $row['name'];
        $playerLastName[] = $row['surname'];
        // print position and open select element

            print $pposition;
            echo '<select>';
            foreach ($playerName as $name) { //assign playername to position element
                echo '<option>' . $name;
                '</option>';
                echo '</select>';
                echo '<br />';
            } //close assign player nae to position loop

    } //end while  loop
} //end opening for each lo

I have removed foreach ($position as $playerPosition) {

You can do it like this. the logic is something different

function get_players_by_position($position)
{
    $query  =   "SELECT
                  `player_id`,
                  `name`,
                  `surname`
                FROM `player_info`
                WHERE `position` = $position
                ORDER BY name   ";

    $result =   mysql_query($query);
    $data   =   array();
    while ($row = mysql_fetch_array($result)) { 
        $data[] =   $row;
    }

    return $data;
}   

foreach($position as $pposition){
    $data   =   get_players_by_position($pposition);
    echo $pposition;    
    echo '<select>';
    foreach($data as $row){
        echo '<option '.$row['id'].'>' . $row['name'].'</option>';
    }
    echo '</select>';
    echo '<br />';
    unset($data);
}