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 | usera@in.in | User Alpha
2 | userB | userb@in.in | User Bravo
3 | userC | userc@im.in | user c
4 | userd | userd@am.in | 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?
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'
)
";
try this
suppose that we are trying to get peoples for userA:id=1
$user_id = 1;
and sql will be
$sql = " SELECT * FROM mnc_users WHERE id 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'
)
";