php mysql query select mutual friends?

[shortysbest]

KickStart;

 

I have just noticed that the mysql you provided me actually isn't working correctly :|.

 

it appears that it is just loading all friends of the users profile you are on, except the session user (which is how it should be), it doesn't only load friends that you both have in common. I thought it had been working perfectly before, but eh dunno what happened, I didn't change any of the code.

 

$mutual_friends = mysql_query("SELECT z.aFriend AS FriendId FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend ");

[kickstart]

Hi

 

Not sure why I made that mistake.

 

Think (looking briefly) this should do it

 

$mutual_friends = mysql_query("
SELECT z.aFriend AS FriendId 
FROM (
SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend 
FROM friends a 
WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z 
INNER JOIN (
SELECT if(a.friend_1 = '$session',a.friend_2,a.friend_1) AS aFriend 
FROM friends a 
WHERE (a.friend_1 = '$session' AND a.friend_2 != '$id') OR (a.friend_2 = '$session' AND a.friend_1 != '$id')) x 
ON z.aFriend = x.aFriend ");

 

First subselect should get all the friends of $id who are not $session, while the 2nd is getting all friends of $session who are not $id. Then the JOIN brings that down to common friends.

 

This might be a bit cleaner (not tested though)

 

$mutual_friends = mysql_query("
SELECT z.aFriend AS FriendId 
FROM (
SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend 
FROM friends a 
WHERE '$id' IN (a.friend_1,a.friend_2)) z 
INNER JOIN (
SELECT if(a.friend_1 = '$session',a.friend_2,a.friend_1) AS aFriend 
FROM friends a 
WHERE '$session' IN (a.friend_1,a.friend_2)) x 
ON z.aFriend = x.aFriend
WHERE z.aFriend NOT IN ('$id','$session')");

 

All the best

 

Keith

[shortysbest]

thank you, seems to work correctly now. Thanks a ton! for your help. I haven't tackled the level of mysql queries yet, which clearly I need to 

 

& ahh, I see the error now. Just switched session and id at the inner join. Glad I looked it over, I fixed the select posts from mutual friends that you made me earlier too. I'm a happy camper.

[hoponhiggo]

Hi Guys

 

Sorry to jump in and restart this topic but it is very similar to a problem i am having and hopefully the answer lies within this one...

 

My query is very similar to the original posts (shortysbest) but he seems alot more confident with sql and Php than myself, and his 'friends' table is slightly different to mine as his uses a userid and mine is a user name.

 

Basically, my table looks like:

 

frienshipID-------friendname--------username

          1 -------------username1--------username2

 

etc

 

How can i dispay the friends of a logged in user??