获取特定用户之后的用户列表，并检查我是否关注他们

Question

I have a mysql table consisting of users following other users.

USER_ID FOLLOW_ID
1        2
1        3
2        4
3        4
6        4
2        6

I am user No. 2 and the person in question is user No. 4. You see that three people are following user No.4 including me. I can query the users who are following user No.4. How can I add to the query if I am following these people or not? So what I would like to know is who are following a specific user (No.4 in this case) and which one of them I am following.

Desired result:

USER_ID (No.4 is FOLLOWED BY), DO_I_FOLLOW_HIM
2                              No
3                              No
6                              Yes

As you see from the last record of the table I (No.2) am following User No.6.

Query the list of people following user No.4:

$myid = '6';
$userid = '4';
    $i = 0;
    try {
        $stmt = $conn->prepare("SELECT USER_ID FROM FOLLOW WHERE FOLLOW_ID=?");

        $stmt -> execute(array($userid));
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $row2[$i] = $row['USER_ID'];
                $i++;               
            }
        } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        $response["success"] = 0;
    }

Sample SQL Fiddle for help: http://sqlfiddle.com/#!2/97ac6

Answer 1

To get a list of users that you follow that are following another given user, you need to use a subquery. You can see it when written above you are asking two things

1. Who is following person A
2. From that list who am I following.

So You could try using a query like so,

Select    User_ID,  Follows_ID
From      Follows
Where     User_ID = ?
And       Follow_ID In (Select User_ID From Follows Where Follow_ID = ?)

To see a list and whether you're following

Select   f.User_ID,
         Case When ? In (Select User_ID From Follows Where Follow_ID = f.User_ID) Then 'Yes' Else 'No' End
From     Follows f
Where    f.Follow_ID = ?

@Shudder makes a good point, you may see performance increases (especially if this is a large table) by using a join instead of a subquery.

SELECT him.user_id, IF(i.follow_id,'yes','no') AS i_follow_him
FROM Follows him
LEFT JOIN Follows i ON (i.follow_id = him.user_id AND i.user_id = ?)
WHERE him.follow_id = ?