most efficient way to print a list of names based off a csv using php and mysql

Question

I have an array of player's IDs. There will generally be about 5 players, not likely to be more then a dozen:

$cplayers= array(1,2,5);

I want to display the players names as a list.

$query = "SELECT username,id FROM users ORDER BY id";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$playercounter =0;
while ( $row = mysql_fetch_array($result) ) {

  if ($row['id'] == $cplayers[$playercounter]) {
    echo "<li>".$row['username']."</li>";
    $playercounter++;
    }
  }

So I'm pretty sure this isn't the most efficient way I could do this. Would it be better to do individual queries? Also is there a good way to exit the while loop once $cplayers is done?

Answer 1

This should only return the players whom you have ids for:

$ids = implode($cplayers);
$query = "SELECT username,id FROM users WHERE id IN(" . $ids . ") ORDER BY id";

Answer 2

Based on how I'm interpreting this, I'd use the MySQL IN clause, e.g.

$id_list= array(1,2,5);
$sql= 'SELECT username FROM users WHERE id IN('. join(",",$id_list) .') ORDER BY id';
$result= mysql_query($sql) OR die(mysql_error());
while($row= mysql_fetch_assoc($result)) {
  echo "<li>{$row['username']}</li>";
}

This targets only those id values in the list, is that what you want?

Answer 3

just change your query to this:

$query = "SELECT username,id FROM users WHERE id IN (".implode(",",$cplayers).")ORDER BY id";

this will return the correct players you're looking for.

Answer 4

Well apparently the answer is rather to normalize my database, have a separate (third) table for the join of the two rather than using an array w/in the second table.