SELECT limiting to only results based on a CSV list from a separate table

Question

So I have a list of CSVs in one table. (EG: 1,3,19 ) I want to search out all of the usernames from the other table where the ids match any of those.

I feel like I should be able to do something like:

<?
$query = "SELECT player_ids FROM cast_list WHERE game='".$gameid."' ";
$result = mysql_query($query) or die(mysql_error());
$playerquery = "SELECT username,id FROM players WHERE id IN (".$result.") ORDER BY username;
$player_result = mysql_query($playerquery) or die(mysql_error());
echo "<ul>";
while ($row = mysql_fetch_array($player_result) ) {
  echo "<li>".$row['username']."</li>";
}
echo "</ul>";
?>

but I can't get it to work. What am I doing wrong?

Answer 1

The idea is correct, but you need to transfer the $result to an actual string array:

$game_ids = array();
while ($row = mysql_fetch_array($result) ) {
 $game_ids[] = .$row[1];
}

Now using implode to convert the array to a comma separated values with a comma:

$playerquery = "SELECT username,id FROM players WHERE id IN (" . implode(",",$result) . ") ORDER BY username;

Answer 2

You can also use a subquery (which will be faster):

$playerquery = "SELECT username,id 
                FROM players 
                WHERE id IN (SELECT player_ids FROM cast_list WHERE game='".$gameid."') 
                ORDER BY username";

Btw if game is an integer field you don't have put quotes (' ') around the value.