在循环内查询数据库

I have multiple dropdown menus. When the user clicks submit button. The isset function in the if clause is triggerd and the following code gets executed

 if(isset($_POST["submit"]))
  {
     $player_id = $_REQUEST['players'];
     var_dump ($player_id);
            for($i=0; $i < sizeof($player_id); $i++) //query database with different player_id each time
            {
                foreach ($player_id as $id){
                    $query = 'SELECT `name`  FROM `player_info`
                    WHERE `player_id` = '.$id;
                    $return_names = mysql_query($query) or die(mysql_error());
                                            }
                        while($row = mysql_fetch_array($return_names))
                        {
                            $selected[] = $row['name'];     
                        }                   
                        var_dump($selected);
                    }
                }

What the above code should do is return the names, of the players, the user selected. However when I open it I get this:

Notice the $player_id array which I use in the 1st var_dump holds the different player_id values.

However when I do a var_dump on the second array $selected the array contains only the values "Burger"

I suspect the problem is in the foreach loop and the way I query the database. If someone could point me in the right direction it would be greatly appreciated. Thanks in advance.

The loop for($i=0; $i < sizeof($player_id); $i++) is an excessive loop. You are not using $i from this loop so you need not it. Just imagine, if your $player_id array has 3 items, you will loop it, and on every step of this looping, you also will loop whole $player_id array one more time by using foreach loop.

Also, you are using unsafe method to pass variables in the query. Best practice I think is acquire a habit to convert income $_POST variables to int if they are suggested be numbers. It's just one string $player_ids = array_map('intval', $_REQUEST['players']);, or $id = (int)$_POST['id'], or $number = (int)$_GET['number'], etc.

Another thing is overquering your database. Instead of creating separate query for every ID, you can use one query with IN operator, which right side is all IDs joined by commas.

Another safe variant of your code is:

if ( isset($_POST['submit']) ) {
    $player_ids = array_map('intval', $_REQUEST['players']);

    //var_dump($player_ids);

    $query = 'SELECT `name` 
        FROM `player_info` 
        WHERE `player_id` IN (' . implode(',', $player_ids) . ')';

    $return_names = mysql_query($query) or die(mysql_error());

    while ( $row = mysql_fetch_assoc($return_names) ) {
        $selected[] = $row['name'];
    }

    //var_dump($selected);
}

Remove the for loop and it should work. You want to iterate over each player id, which you are doing using foreach. The for outside it is not needed.

Use the Force, Luke! With abstraction library like safeMysql it will be two lines of code

$sql = 'SELECT name  FROM player_info WHERE player_id in (?a)';
$names = $db->getCol($sql,$_POST['players']);

And, unlike yours - it is safe from injection.

You're essentially running the same loop twice. Take out

for($i=0; $i < sizeof($player_id); $i++){} 

You' re double-looping for no reason.

Also, while you execute one SELECT for each player id, you're trying to iterate the result outside foreach, which means that you end up iterating only the last one.

Moreover, none of your SELECTs need iteration, provided that they produce at most one row.

Try it like this:

if(isset($_POST["submit"]))
  {
  $player_id = $_REQUEST['players'];
  var_dump ($player_id);
  foreach ($player_id as $id)
    {
    $query = 'SELECT `name`  FROM `player_info` WHERE `player_id` = '.$id;
    $return_names = mysql_query($query) or die(mysql_error());
    $row=mysql_fetch_array($return_names); // at most one -- or not?
    if($row)
      {
      $selected=$row['name'];     
      var_dump($selected);
      }
    else
      echo "Player with id $id not found in DB!";
    }
  }