[RESOLVED] Double array loops

drag0n

Hello
In this script Im trying to write; Im trying to fetch the id of certain users from one table then using the ids that Ive fetched, I want to fetch the users information from another table (the main users table).
However, the trouble Ive run into is that I would have to run double loops to fetch all the information...
Up til now, I have the current code:

$UserIDQry = mysql_query("SELECT * FROM user_subjects WHERE code='$code' AND misc='NULL'");
while($UsersID = mysql_fetch_array(UserIDQry))
	{
		$UserInfoQry = mysql_query("SELECT * FROM users WHERE user_id='$UsersID[user_id]'");
$UserInfo = mysql_fetch_array($UserInfoQry);
              //This is where I am confused as to what to do...
              }

Should I run another loop inside the first "while" loop? How should I write it?
Basically all I want to do is grab all the users ids from one table, then using those user ids, grab all the user information from another table and then populate a dropdown list with the users first and last name (gotten from the main users table)

Thanks in advanced for any type of help!

benbox

You need something like this:

<?php
$UserIDQry = mysql_query("SELECT * FROM user_subjects WHERE code='$code' AND misc='NULL'"); 
while($UsersID = mysql_fetch_array($UserIDQry)) { 

$id = $UsersID['user_id'];
$UserInfoQry = mysql_query("SELECT * FROM users WHERE user_id='$id'"); 
while($userInfo = mysql_fetch_array($UserInfoQry)) {
	$name = $userInfo['name']; // or whatever else you want to do here
}

} 
?>

bogu

Try a LEFT JOIN:

SELECT * FROM user_subjects us
LEFT JOIN users u USING(user_id)
WHERE us.code = '$code' AND us.misc='NULL'

benbox

But saying that, assuming only one user is going to be returned (if by an ID I would have thought so..) then you don't need the loop.. and could do something like this:

<?php
$UserIDQry = mysql_query("SELECT * FROM user_subjects WHERE code='$code' AND misc='NULL'"); 
while($UsersID = mysql_fetch_array($UserIDQry)) { 
	$id = $UsersID['user_id'];
	$UserInfoQry = mysql_query("SELECT * FROM users WHERE user_id='$id'"); 
	$name = mysql_result($UserInfoQry, 0, 'name'); // or whatever else you want to do here
} 
?>

Or then again, Bogu's method looks a LOT more efficient!

drag0n

left join worked great!
thanks for the help people