在一个sql语句中结合我的php代码和mysql语句

Question

I have created a "suggested friends" php script, similar to facebook. My database has 2 tables, users(user_id, name, surname, profile) and friends(friends_id, user_one, user_two). My code is the following:

<?php

//----- gets all friends of my friends -------

$friends_of_friends = mysql_query(" SELECT u.*
        FROM (SELECT DISTINCT user_one as user_id
            FROM friends
            WHERE user_two IN (SELECT user_one as user_id
                         FROM friends
                     WHERE user_two = '$session_user_id'
                     UNION DISTINCT
                     SELECT user_two
                     FROM friends
                     WHERE user_one = '$session_user_id'
                       )
            UNION DISTINCT
            SELECT DISTINCT user_two
            FROM friends
            WHERE user_one IN (SELECT user_one as user_id
                     FROM friends
                     WHERE user_two = '$session_user_id'
                     UNION DISTINCT
                     SELECT user_two
                         FROM friends
                     WHERE user_one = '$session_user_id'
                       )
           ) f
          JOIN users u
          ON u.user_id = f.user_id  ");

while ($run_friends= mysql_fetch_assoc($friends_of_friends)) {
    $friend_friend_id = $run_friends['user_id'];


 // ---- gets friends of my friends that are not my friends -------------------------------

 $check_friend_query = mysql_query("  SELECT friends_id from friends WHERE  (user_one='$session_user_id' AND user_two='$friend_friend_id') OR (user_one='$friend_friend_id' AND user_two='$session_user_id')   ");

   if (mysql_num_rows($check_friend_query) != 1){ 

    $not_friends = mysql_query("SELECT `user_id`, `name`, `surname`, `email`, `profile` FROM `users` WHERE (`user_id`='$friend_friend_id' AND `user_id`!='$session_user_id')  ");

       while ($run_not_friends= mysql_fetch_assoc($not_friends)) {
         $not_friend_id = $run_not_friends['user_id'];
                 echo $not_friend_id;
       } //end while

   } //end if

} //end while

?>

My code works fine and gives me all friends of my friends that I want. Is it possible to combine all the above code in one spl statement? Any idea how to do this?

Answer 1

Your query seems awefully complex for what you are trying to achieve. As far as I can tell the only fields that are relevant for your original query are user_one and user_two in friends and user_id in users. By using sub-queries, the runtime of the query will increase exponantially.

That would mean you could rewrite it to the following query, which should be a lot faster, because mysql can short-circuit out a lot of results instead of having to do every single sub-query. It returns all users that are friends from friends from a certain user I'll call 'x' from here on. Friends of 'x' that are not a friend of a friend are not returned and because of the AND at the end, it will not return the user itself either.

SELECT DISTINCT c.* FROM friends as a, friends as b, users as c
WHERE (a.user_one = 1 AND (
  (a.user_two = b.user_one AND b.user_two = c.user_id) OR
  (a.user_two = b.user_two AND b.user_one = c.user_id)
  ) OR (a.user_two = 1 AND (
  (a.user_one = b.user_one AND b.user_two = c.user_id) OR
  (a.user_one = b.user_two AND b.user_one = c.user_id)
  )
 )) AND c.user_id != 1
ORDER BY c.`user_id` ASC

To remove all users that are direct friends from 'x' from the results you can use NOT IN( ... ). I use a sub-query for this, as I don't believe there is a way to do this without sub-queries. I could have joined an extra friends table, but even if I test if the current user is a friend from c.user_id against that newly joined table, the query could procceed by matching any of the other friends in that part of the query, which is something we don't want. The sub-queries will (or should) execute exactly once per unique friend-from-friend. I think the performance would be better if you had those queries seperate and compared both returned arrays with each other, but that would not allow you to use LIMIT in the query.

SELECT DISTINCT c.* FROM friends as a, friends as b, users as c
WHERE (a.user_one = 1 AND (
  (a.user_two = b.user_one AND b.user_two = c.user_id) OR
  (a.user_two = b.user_two AND b.user_one = c.user_id)
  ) OR (a.user_two = 1 AND (
  (a.user_one = b.user_one AND b.user_two = c.user_id) OR
  (a.user_one = b.user_two AND b.user_one = c.user_id)
  )
 )) AND c.user_id != 1 AND
 c.user_id NOT IN (
   SELECT friends.user_two FROM friends WHERE friends.user_one = 1 UNION
   SELECT friends.user_one FROM friends WHERE friends.user_two = 1
 )
ORDER BY c.`user_id` ASC

I believe this should link to the correct sqlfiddle: http://sqlfiddle.com/#!2/6c14e/2