title: Suggest friends based off of mutual friends
body: <p>I've already looked around on this site, but none seem to be exactly what I'm looking for. A lot of them are talking about how Facebook does it, or how Twitter does it to suggest followers, but even then they don't give any straight-forward answer.<br>
  All of the ones I find are just calculating mutual friends using two user IDs.</p>
  
  <p>I want to be able to take the logged in user's ID, go through their friends, and go through THEIR friends to calculate the ones who have the most mutual friends with the logged in user, to suggest people to add that have the most mutual friends. </p>
  
  <p>I'm using PHP and MySQL for this. I just can't seem to wrap my head around how I'd do it. </p>
  
  <p>My friendship table looks something like this: </p>
  
  <pre><code>---------------------------------
  | friend1 |  friend2  | pending |
  ---------------------------------
  |    1    |     2     |    0    |
  |    2    |     1     |    0    |
  |    3    |     1     |    0    |
  |    1    |     3     |    0    |
  ---------------------------------
  </code></pre>
  
  <p>This table is showing that user_id 1 is friends with 2 and 3<br>
  and 2 isn't friends with 3, but is friends with 1.</p>
  
  <p>So, if the user was logged into user_id 2, I want it to suggest user_id 3 because both of them are friends with user_id 1. </p>
  
  <hr>
  
  <p>What I have so far:<br></p>
  
  <pre><code>  public function friendList($user_id = null){
      if(!$user_id){
        $user_id = $this-&gt;_data-&gt;user_id;
      }
      $query = "SELECT friend2 FROM user_friends WHERE pending = 0 AND ((friend1 = ".$user_id.") AND (friend2 IN (SELECT user_id FROM users WHERE active = 1 AND user_id = friend2)))";
      $data = $this-&gt;_db-&gt;hardquery($query);
      return $data-&gt;results();
    }
    public function suggestUsers(){
      $user_id = $this-&gt;_data-&gt;user_id;
      $my_friends = array();
      $suggest_friends = array();
      foreach($this-&gt;friendList() as $friend){
        array_push($my_friends,$friend-&gt;friend2);
      }
      foreach($my_friends as $friend_id){
        foreach($this-&gt;friendList($friend_id) as $friendOfFriend){
          $friendOfFriend = $friendOfFriend-&gt;friend2;
          if(!in_array($friendOfFriend,$my_friends) &amp;&amp; $friendOfFriend != $user_id){
            array_push($suggest_friends,$friendOfFriend);
          }
        }
      }
      foreach($suggest_friends as $sgf){
        $sgf = new user($sgf);
        $sgf = $sgf-&gt;data();
        echo "&lt;a href=\"#\"&gt;".$sgf-&gt;display."&lt;/a&gt;&lt;br&gt;";
      }
    }
  </code></pre>
  
  <hr>
  
  <p>and it works, listing off friends who are friends of the user's friends, but don't have the user added...<br>
  HOWEVER, I can't sort it based off of who has the most mutual friends, which I guess is okay (Though I'd like if I could), <br>but it also doesn't seem like a very efficient way to do it.. </p>
  
  <p>It seems like this would take a lot of resources having to go through all of the user's friends, especially if the user has a couple hundred to a thousand friends added, and they have a couple hundred to a thousand added, etc. </p>
  
  <p>I'm not that familiar with advanced SQL, so I'm not sure how I'd go about doing this.</p>