如何为“你可能知道的人”构建查询[关闭]

Question

i need to show "people you may know" based on the current friends of a user and the friend suggestion should consider blocked users,if user A blocked user B,A should not see user B or vice versa in their friends Suggestions here is my table structure

mnc_users

id  |   username    |   email       |   fullname
---------------------------------------------------
1   |   userA       |   [e-mail] |   User Alpha
2   |   userB       |   [e-mail] |   User Bravo
3   |   userC       |   [e-mail] |   user c
4   |   userd       |   [e-mail] |   user del

mnc_friends

uid |   fid |   time
-------------------------
1   |   3   |   1363859398  
3   |   1   |   1363859398  
4   |   1   |   1368854898  
1   |   4   |   1368854898  
3   |   2   |   1363846116  
2   |   3   |   1363846116  
2   |   4   |   1363846116  
4   |   3   |   1363846116  

mnc_blocked

id  |   uid |   bid
--------------------
1   |   1   |   2

mnc_frnd_rqsts

uid |   fid
-----------
5   |    1

how can i display users suggestions in this case using php and mysql?

Answer 1

if u edit this answer to add couple of things you will get it working

$user_id = 1;

    $sql = "    SELECT * FROM mnc_users WHERE id NOT IN(
                                             SELECT fid FROM mnc_friends WHERE uid='$user_id' AND fid!='$user_id'
                                            )
                                      AND id NOT IN(
                                            SELECT bid FROM mnc_blocked WHERE uid='$user_id'
                                           )
AND id NOT IN(
                           SELECT fid FROM mnc_frnd_rqsts WHERE uid='$user_id'
                                           )
       ";