Click to See Complete Forum and Search --> : Picking a field in a row when it appears in another row


Hen Asraf
12-25-2008, 06:38 AM
Couldn't really think of a descriptive title, sorry!
What I have is this table...friends => friend, befrienderNow what I want to do, is fetch a list of mutual friends. That means that I need to fetch a 'friend' field where the befriender ID appears once as a person and on another row the befriender is me, where they both have the same 'friend' value.
What I have right now is...function mutual_friends($id)
{
$mutual = array('');
$result = mysql_query("SELECT * FROM `friends` WHERE `befriender` = '$id'");
while ($friend = mysql_fetch_array($result))
{
$result = mysql_query("SELECT * FROM `friends` WHERE `befriender` = '".fetch_user_id()."' AND `friend` = '{$friend['friend']}'");
if (mysql_num_rows($result) == 1)
{
$cur = mysql_fetch_row($result);
array_push($mutual, $cur[0]);
}
}
return $mutual;
}But I'm sure there's an easier way to do this with an SQL JOIN or something to that amount, I'm just not sure what to do about it. Help?