Triumph Oduro Triumph Oduro - 7 months ago 36
SQL Question

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

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

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.