MySQL query help

[connex]

Here is a quick idea of what i'm trying to do.

I want to do a query, which gets all the id's from one table.

Once i have those, I want to use those values and query another table and output the results.

 

I have already made 2 queries, and they both work seperatly, but when i nest them they stop functioning... Only the first row is output.

 

Any idea on how I should go about this? Heres the code i used below if it helps:

 

<?php
// get query
$query = "SELECT gameid FROM scores ";
$query .= "WHERE userid = '{$user_id}' ";
$query .= "ORDER BY gameid ASC ";
$query .= " LIMIT 0 , 100 ";
$results = mysql_query($query, $connection) or die();

while ($scores = mysql_fetch_array($results)) {

	// get query
	$query = "SELECT gamename FROM games ";
	$query .= "WHERE id = '{$scores['gameid']}' ";
	$query .= "ORDER BY gamename ASC";
	$results = mysql_query($query, $connection) or die();

	while ($gamedata = mysql_fetch_array($results)) {
		echo "<a href=\"http://apps.facebook.com/highscorearcade/play/play_game.php?gameid={$scores['gameid']}\">";
		echo "{$gamedata['gamename']} </a><br />";

		}
}
	?>

[jamkelvl]

1. Where is $scores['gameid'] set?

2. I'm not 100% I understand what you're trying to do, or how your code works but..  try this.

<?php
// get query
$query = "SELECT gameid FROM scores ";
$query .= "WHERE userid = '{$user_id}' ";
$query .= "ORDER BY gameid ASC ";
$query .= " LIMIT 0 , 100 ";
$results = mysql_query($query, $connection) or die();
while ($scores = mysql_fetch_array($results)) {
// get query
$query = "SELECT gamename FROM games ";
// changed the below..
$query .= "WHERE id = '{$gameid}' ";
$query .= "ORDER BY gamename ASC";
$results = mysql_query($query, $connection) or die();
while ($gamedata = mysql_fetch_array($results)) {
echo "<a href=\"http://apps.facebook.com/highscorearcade/play/play_game.php?gameid={$scores['gameid']}\">";
echo "{$gamedata['gamename']} </a><br />";
}
}
?>

 

[jl5501]

The main problem is you are overwriting the variable $results.

 

You need to pick a different variable name for the inner loop

[connex]

$scores['gameid']  is a value from the previous query.

 

The first query runs and pulls out one array of data. It then executes the second query.

 

The second query is supposed to use this value to execute its own query.

Once the second query is done, i expect the first query to execute the whole cycle again until it reaches the end of its list.

 

Is that more clearer?

 

@ jl5501 - THANK YOU! I had tried that before but forgot to change it in all places so thought it wasnt that. Ive managed to get it to work now. Thanks a lot guys!