Writing a MYSQL Following Users Query

Question

I'm building an app where a user can follow other users and be followed.

A user can also look at who another user is following.

Now lets say user1 is looking at who user2 is following, I need to find all the people IDs that user2 is following and compare it against who user1 is following.

Instead of returning only the IDs of all the users that match both user1 and user2 (which I've seen in other forums), I need to retrieve all user2's following IDs and User Names as well as a flag that indicates if the followed person is also followed by user1.

I've got it to work in PHP with a double for loop of each Query, but I worry that this code will be expensive and would be far better optimized with a single MYSQL query.

Relevant tables and columns:

following_table
  follower_id
  followed_id
  following: varchar -- 'true' or 'false'

user_table
  user_id
  user_name

Here is my PHP code:

$user_id1 = '1991';
$myFollowingQuery = "SELECT following_table.followed_id, user_table.user_name
                     FROM following_table 
                     INNER JOIN user_table ON
                     following_table.followed_id = user_table.user_id
                     WHERE following_table.following = 'true'
                     AND following_table.follower_id = '$user_id1'";

$user_id2 = '1985';
$userFollowingQuery = "SELECT following_table.followed_id, user_table.user_name
                       FROM following_table 
                       INNER JOIN user_table ON
                          following_table.followed_id = user_table.user_id
                       WHERE following_table.following = 'true'
                       AND following_table.follower_id = '$user_id2'";

$userFollowingResult = mysql_query($userFollowingQuery) 
                             or doResponse('error',"Couldn't connect to the database");
$myFollowingResult = mysql_query($myFollowingQuery) 
                             or doResponse('error',"Couldn't connect to the database");

            for($i = 0; $i< mysql_num_rows($userFollowingResult);$i++){

$loopArray = array(followed_id => mysql_result($userFollowingResult,$i,"followed_id"),
                   followed_name => mysql_result($userFollowingResult,$i,"user_name"));

for($j = 0; $j< mysql_num_rows($myFollowingResult);$j++){
  if(mysql_result($userFollowingResult,$i,"followed_id")
     ==mysql_result($myFollowingResult,$j,"followed_id")) {
     $loopArray['is_following'] = 'true';
     break;
  }
  if($j==mysql_num_rows($myFollowingResult)-1){
    $loopArray['is_following'] = 'false';
    break;
  }
}

$resultArray[$i] = $loopArray;
}

echo json_encode($resultArray);

I'm not sure what you mean, is it like a mutual followings listing you want? — Jonathan
– Jonathan, Commented Jul 6, 2012 at 0:25
Can you please paste the table structure too (show create table TABLE_NAME). From what I can guess you can use MINUS, INTERSECT to resolve your problem. — golja
– golja, Commented Jul 6, 2012 at 0:48
Sorry to be a newbie. Never used (show create table TABLE_NAME). Tried to get it to work with Querious but got no results. Basically the following_table consist of: follower_id | followed_id | date_created | following(true or false.) the user_table consists of: user_id | user_name | followers | following | etc., etc.. — Peter Tucker
– Peter Tucker, Commented Jul 6, 2012 at 1:44

biziclop · Accepted Answer · 2012-07-06 04:55:21Z

1

Here is a simplified query:

http://sqlfiddle.com/#!2/6b8d6/3

SELECT
  user.user_id,
  user.user_name,
  he.follower_id AS follower_id,
  IF(me.followed_id,1,0) AS metoo

FROM       following AS he

INNER JOIN user
   ON user.user_id = he.followed_id

LEFT  JOIN following AS me
   ON me.follower_id = 1
  AND me.followed_id = he.followed_id

WHERE he.follower_id = 2

edited Jul 6, 2012 at 4:55

answered Jul 6, 2012 at 4:27

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Tucker Over a year ago

Very cool. Is there anyway to get he.followed_name from the user_table? This is the Final JSON I'm trying to get: {"followed_id":"27","followed_name":"userNameHere","is_following":"true"}

Peter Tucker Over a year ago

You rock so much! That worked perfectly. Thank you so much for your help.

biziclop Over a year ago

Of course it works only, when (follower,followed) is a unique pair in the followers table, otherwise the above query would make some duplicate rows.

Justin Erswell Over a year ago

@biziclop is there any chance in posting a SQLFiddle showing a situation where in the same way user1 is looking at users that follow user2 and show the users that user 1 is following in a boolean? I have been struggling with this for a while and your code here gets me 80% of the way. Thanks!

Collectives™ on Stack Overflow

Writing a MYSQL Following Users Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related