User’s friends + friend’s common test with another user

Question

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

user_1 | user_2 | status 

where the "status" can be -1.0 or 1. Here we will only consider cases when the status is "0" (expected for user_1) or "1" (approved by user_2). I have the following query to search for pending / approved friends for this user $:

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

The goal is to modify the request to also say if a given $ user2 is a regular (approved) friend of $ user1 and every (approved) friend of $ user1.

After some research, I realized that a left join will do the trick by setting the other field to NULL (if there is no mutual) or $ user2. I would like to do it effectively. I tried a few shots, but did not succeed.

Thanks in 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 'a', check if 'b' is a regular friend of f and 'a'. In addition, 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 the MySQL query, it would be so difficult and slow that I would not use it myself, here is the 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 note that it has not been tested. May contain a minor syntax error, but should return an array of common friends.

Answer 2

I slightly modified the Flash Thunder feature post. Just tested with some changes 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; }