Friends of a user + mutual friend test with another user

Question

I have this table that I use (but not only) for storing friends in a database :

user_1 | user_2 | status

where 'status' can be -1,0 or 1. Here, we will consider only cases where status are '0' (pending for user_1) or '1' (approved by user_2). I have the following query to look for pending/approved friends for a given $user :

SELECT user_1,user_2,status
         FROM Friends
         WHERE (user_2 = '$user' OR user_1 = '$user') AND status >= 0;

The goal here is to modify the query to also tell if a given $user2 is a common (approved) friend of $user1 and each (approved) friend of $user1.

After some researches, I figured out that the left join would do the trick, by setting another field to either NULL (if no mutual) or $user2. I would want to do it efficiently. I tried several shots, but no success around it.

Thanks by advance for your help

EDIT : For example, let's say we have the following entries :

a | b | 1
c | a | 1
c | b | 1
a | d | 1

I want to list the friends of 'a' and for each friend f of 'a', verify if 'b' is a common friend of f and 'a'. Also, f =/= b for the mutual test. The result of such a query would be :

a | b | 1 | NULL
c | a | 1 | b
a | d | 1 | NULL

Let me know if you need more clarification

Answer 1

As in MySQL query would be so complicated and slow, that I wouldn't use it myself, here's a solution in PHP, with only one query:

<?php

// $db = mysqli_connect(...);

function findMutualFriends($of,$mutual_with){
    global $db;
    $user_friends = array();
    $mutual_friends = array();
    $results = array();

    $res = mysqli_query($db,"SELECT user_1,user_2,status FROM Friends WHERE ((user_2 = '$of' OR user_1 = '$of') OR (user_2 = '$mutual_with' OR user_1 = '$mutual_with')) AND status >= 0;";
    while($row = mysqli_fetch_assoc($res)){
        if($row['user_1'] == $of || $row['user_2'] == $of){
            $user_friends[] = (($row['user_1'] == $of) ? $row['user_2'] : $row['user_1']);
        }
        if($row['user_1'] == $mutual_with || $row['user_2'] == $mutual_with){
            $mutual_friends[(($row['user_1'] == $mutual_with) ? $row['user_2'] : $row['user_1'])] = 1;
        }
    }
    foreach($user_friends as $friend){
        if($mutual_firends[$friend]){
            $results[] = $friend;
        }
    }
    return $results;
}

?>

Please notice that it haven't been tested. May contain some minor syntax error, but should return an array of mutual friends.

Answer 2

I modified a bit the Flash Thunder's function post. Just tested with some modifications and it works ! Thanks again.

function findMutualFriends($pdo, $of,$mutual_with){

    $user_friends = array();
    $mutual_friends = array();
    $results = array();

    $query = "SELECT user_1,user_2,status FROM Friends WHERE ((user_2 = '$of' OR user_1 = '$of') OR (user_2 = '$mutual_with' OR user_1 = '$mutual_with')) AND status = 1;";
    $prep = $pdo->prepare($query);
    $res = $prep->execute();
    $rows = $prep->fetchAll();
    foreach ($rows as $row) {
        if($row['user_1'] == $of || $row['user_2'] == $of) {
            $user_friends[] = ($row['user_1'] == $of ? $row['user_2'] :$row['user_1']);
        }
        if($row['user_1'] == $mutual_with || $row['user_2'] == $mutual_with) {
            $mutual_friends[($row['user_1'] == $mutual_with ? $row['user_2'] :$row['user_1'])] = true;
        }
    }

    foreach($user_friends as $friend) {
        $results[$friend] = $mutual_friends[$friend] == true ? true : false;
    }
    return $results; 
}