Can this double query be shortened?

[tommyda]

Can the following code be shortened to use just one query?

 

At the moment it grabs a list of contacts from the db then checks the database for the users details, i know there is a way to combine the 2 querys into one but i cant remember how its done, any help would be much appreciated.

 

Thanks

 

Tommy

 

	$getcontacts = mysql_query("SELECT contactID FROM usergroups WHERE groupID = '$_GET[groupID]'")or die(mysql_error());
if(!mysql_num_rows($getcontacts)> 0)
echo"No contacts in this group!";
else
while($contact = mysql_fetch_array($getcontacts))
{
	$getcontact = mysql_query("SELECT * FROM users WHERE userID = '$contact[contactID]'")or die(mysql_error());
	$user = mysql_fetch_assoc($getcontact);

echo"$user[fullName] - $user[username] - $user[email] - $contact[contactID]<br/>";
};

[Adam]

Yeah:

 

SELECT users.* FROM usergroups
JOIN users ON contactID = userID
WHERE groupID = 123

[tommyda]

thanks but i get the error "Column 'userID' in on clause is ambiguous"

 

 

[Adam]

All it means is that MYSQL doesn't know from which table `userID` you're referring to. You can fix it by referencing the table name before the column name, or by using an alias of the table name to keep it cleaner:

 

SELECT u.* FROM usergroups ug
JOIN users u ON ug.contactID = u.userID
WHERE ug.groupID = 123