SELECT limiting to only results based on a CSV list from a separate table
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 m开发者_如何转开发atch 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?
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.
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;
精彩评论