i have two tables in my database one is to keep users info (users_table ) and the other one keeps track of the friends
users_table:
id username avatar
1 max max.jpg
2 jack jack.jpg
friends_table :
id u1_id u2_id
1 1 2
2 1 3
in every user profile i show his/her friends list
here is my query
select u.id,
u.username,
u.avatar
from friends_table f
join users_table u on f.u1_id = u.id || f.u2_id = u.id
where u.id <> $profile_id
and (f.u1_id = $profile_id || f.u2_id = $profile_id)
this query selects friends of the profile owner ($profile_id)
and join them with the user table to get each friend username and avatar
now i want to count the mutual friends between each friend and the profile owner is it possible to this in one query or should i do some long and probably slow query like this for each founded friend( it's just a example and it might have some syntax error ):
foreach ( $friends_list_query_resul as $qr ){
$friend_id = $qr['id'];
$mutual_count = mysql_query
( "select count(*) from friends_table where
($u1_id = $friend_id || $u2_id = $friend_id )
&&
( $u1_id IN ( SELECT `u1_id`,`u2_id` from friends_table where
($u1_id = $profile_id || $u2_id = $profile_id ) )
||
$u2_id IN ( SELECT `u1_id`,`u2_id` from friends_table where
($u1_id = $profile_id || $u2_id = $profile_id ) )
")
}
Head to the Timeline of any current Facebook friend, and click the "Friends" tab. Before you even click, you'll see a number indicating how many mutual friends you two share. Now click "Mutual Friends," and you'll be greeted with a full list of all the connections you two have in common.
To search for people who are already friends with your other Facebook friends, go to the Mutual Friend section and check the names of one or more friends. (If a particular friend isn't listed, enter his or her name into the text box first.)
The number of mutual friends now includes mutual friends whose accounts have been disabled, but these friends are not displayed. Also, if the person's full list of friends is hidden to you, a mutual friend who also has their full list of friends hidden to you will not be displayed as a mutual friend.
a person who is the friend of two people who may or may not know each other: Lynn and Phil met through a mutual friend. SMART Vocabulary: related words and phrases. Friends, acquaintances & contemporaries.
Your first query could also be written as:
select distinct u.id,
u.username,
u.avatar
from users_table u where u.id in
(select case when u1_id=$profile_id then u2_id else u1_id end
from friends_table f where case when u1_id=$profile_id
then u1_id else u2_id end =$profile_id);
The mutual friends query can be written as a single query in similar fashion:
select u.id, (select count(f.id) from friends f where
case when f.u1_id=u.id then u2_id else u1_id end in
(select distinct case when u1_id=$profile_id then u2_id else u1_id end
from friends where case when u1_id=$profile_id then u1_id else u2_id
end =$profile_id)
and u1_id=u.id or u2_id=u.id and
(u1_id <> $profile_id and u2_id <> $profile_id))
as mutual_frnds from user u where u.id <> $profile_id;
but you might want to performance test either of them before using.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With