Mysql query to get a Mutual friend count and display it in a table in php

Question

I have a table called myfriends with 2 columns:

friend_id1
friend_id2

When someone is a friend of some there will be two records. For example lets take 4 members: 1000, 1001, 1002 and 1003.

if 1000 and 1001 are friends with each other there will be two records: (1000, 1001) and (1001, 1000). For the sake of the example, let's assume that 1000 is friends with 1002, as well.

I display the people who are not friends with the logged in user (For instance, 1000), and I want to display the mutual friend count next to each one. considering the example, I need to display 1 in front of 1002 and 0 in front of 1003.

This is the part of a code and the query that I use to list all the members with the add as friend button next to the name inside a table.

$query ="SELECT profile_name,friend_id from friends
                    WHERE friend_id<>'$friendID'
                    AND
                    friend_id NOT IN( SELECT friend_id2 from myfriends WHERE friend_id1='$friendID')";

$results = @mysqli_query($conn, $query) or die
$row = mysqli_fetch_row($results);

echo "<table width='50%' border='1'>";
while ($row) {
    echo "<tr><td>{$row[0]}</td>";
    ?>
    <td><button onclick = "window.location.href='friendadd.php?addfriend=<?php echo $row[1];?>'">Add as friend</button></td></tr>
    <?php
    $row = mysqli_fetch_row($results);
}
echo "</table>";

I want to display the mutual friend count of each one in between the profile name and the add as a friend button.

i m stuck in the query part of addin mutual friend column to the table.i dont know how to get the mutual friend count. — koshi18
@koshi18: That seems silly to me. Why do you have a double record of everything? You're wasting (effectively 100% of the) space. — Madara's Ghost

Bojan Dević Bojan Dević · Accepted Answer · 2013-05-18T11:55:17

Try this:

SELECT
   f.profile_name,
   f.friend_id,
   COUNT(DISTINCT m2.friend_id1) AS count_mutual
FROM 
   friends AS f
   LEFT JOIN myfriends AS m1
     ON (m1.friend_id1 = f.friend_id)
   LEFT JOIN myfriends AS m2
     ON (m1.friend_id2 = m2.friend_id1 AND m2.friend_id2 = '$friendID')
WHERE 
   f.friend_id <> '$friendID' 
   AND f.friend_id NOT IN(SELECT 
                           friend_id2 
                        FROM 
                           myfriends
                        WHERE 
                           friend_id1= '$friendID')
GROUP BY
    f.friend_id

You can see that its working on http://sqlfiddle.com/#!2/fc893/2/0. In order to have the count of mutual friends you need 2 joins with myfriends table, one to get the list of all the friends of the people you are selecting and second to check who of those people is friends with $friendID guy.

Mysql query to get a Mutual friend count and display it in a table in php

2 Answers