How do i query for mutual friends using the same table?

Question

I have table friends and table users

All the ids in friends table (user_one, user_two) are valid users from table users

Lets says a current LoggIn user visit another user's profile and the profile id which he visited is 27

Please Note: I am using the $_GET global variable to get this profile id. e.g

$user_id = $_GET['id'];

then he click on a link to view the friends of this user(27)

so now his friends are:18 and 33

Please how can I query database if current(18) user is also friends with user 33 ?

Note: user 18 is the current LoggIn user

below is clear friends table structure:

u1 | u2

18 | 33

33 | 27

27 | 18

below is my code i tried :

SELECT IF(user_one = '$IsLoggIn' OR user_two = '".$_GET['id']."', user_two, user_one)
FROM friends
WHERE ((user_one = '$IsLoggIn' OR user_two = '".$_GET['id']."') OR (user_two ='$IsLoggIn' OR user_two = '".$_GET['id']."')

Please am new to sql. Thank you

Answer 1

I'm sure this is not the fastest solution (at all...) but it works at least.

I've implemented it with statements to avoid SQL injections as Niet mentioned in the comments.

PHP code:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("
select
    user_two as friend_id,
    if(exists(
        select *
        from friends
        where (user_one = friend_id and user_two = ?)
            or (user_two = friend_id and user_one = ?)),
        'yes', 'no') as mutual
from friends
where user_one = ? and user_two <> ?
union
select
    user_one as friend_id,
    if(exists(
        select *
        from friends
        where
            (user_one = friend_id and user_two = ?)
            or
            (user_two = friend_id and user_one = ?)),
        'yes', 'no') as mutual
from friends
where user_two = ? and user_one <> ?
");
$stmt->bind_param("iiiiiiii", $myUserId, $myUserId, $visitedUserId, $myUserId, $myUserId, $myUserId, $visitedUserId, $myUserId);

// set parameters and execute
$myUserId = $IsLoggIn;
$visitedUserId = $_GET['id'];
$stmt->execute();

// bind variables to prepared statement
$stmt->bind_result($visitedUserFriendsId, $isMutual);

// fetch values (here you can do whatever you want with results)
while ($stmt->fetch()) {
    printf("%s %s\n", $visitedUserFriendsId, $isMutual);
}

$stmt->close();
$conn->close();
?>

If you have this relationships in the table friends (27 is friend of 25, but 18 is not):

user_one | user_two
      18 |       33
      33 |       27
      27 |       18
      27 |       25

when user 18 visits user 27 profile, that SQL query will return:

friend_id | mutual
       25 |     no
       33 |    yes

And then you can work very easily with those results in PHP.

Answer 2

I know you already have the answer. I will use the same PHP code as @nanocv for you to make it short and easier to understand.

You can try this query instead (this query will return all friends of user profile visited, giving a second column as mutual: yes or no):

$stmt = $conn->prepare("
SELECT IF(f.user_one = ?,f.user_two,f.user_one) AS friend_id,     
IF((
    SELECT count(*) 
    FROM friends mf WHERE 
    (mf.user_one = friend_id AND mf.user_two = ?) OR 
    (mf.user_two = friend_id AND mf.user_one = ?) OR 
    (friend_id = ?) /* this return who is visiting as mutual! */

) > 0,'yes','no') AS mutual
FROM friends f 
WHERE (f.user_one = ? OR f.user_two = ?)
/* the next line will not return who is visiting as friend, 
   remove it if you want it to be return as friend */
AND (f.user_one != ? AND f.user_two != ?)
");

$stmt->bind_param('iiiiiiii', $visitedUserId, $myUserId, $myUserId,$myUserId, $visitedUserId, $visitedUserId, $myUserId, $myUserId);

IF the data in friends table does repeat like:

| u1 | u2 |
|----|----|
|  2 | 1  |
|  1 | 2  |

You will need to add a GROUP BY friend_id at the end of the query.

This query will return ONLY the friends of the user profile visited and the user logged in that are mutual:

$stmt = $conn->prepare("SELECT IF(f.user_one = ?,f.user_two,f.user_one) AS friend_id
    FROM friends f 
    WHERE (f.user_one = ? OR f.user_two = ?) AND 
    IF((
        SELECT count(*) 
        FROM friends mf WHERE 
        (mf.user_one = IF(f.user_one = ?,f.user_two,f.user_one) AND mf.user_two = ?) OR 
        (mf.user_two = IF(f.user_one = ?,f.user_two,f.user_one) AND mf.user_one = ?)

    ) > 0,TRUE,FALSE);");

    $stmt->bind_param('iiiiiii', $visitedUserId,$visitedUserId,$visitedUserId,$visitedUserId, $myUserId,$visitedUserId, $myUserId);