I recommend to insert two rows into Relations
table.
id|user_id|friend_id|status(0 and 1)
1 |1 |2 |1(if accepted)
2 |2 |1 |1(if accepted)
For example, user A send friend request to B. Until B accept this request, the status
of both rows would be 0
.
Query for accepting: (user B)
UPDATE Relations SET status=1 WHERE (user_id=$user_id AND friend_id=$friend_id) OR (user_id=$friend_id AND friend_id=$user_id)
Query for selecting:
SELECT Relations.friend_id, Users.name, Users.email, Users.pic_url FROM Relations
INNER JOIN Users
ON Relations.friend_id = Users.id
WHERE Relations.user_id = $user_id
AND Relations.status = 1
Update: If you really want to insert only one row, you would need to execute two queries - the one for the friends user B added and another one for the friends that has added User B to their friend list.
SELECT Relations.friend_id, Users.name, Users.email, Users.pic_url FROM Relations
INNER JOIN Users
ON Relations.friend_id = Users.id
WHERE Relations.user_id = $user_id
AND Relations.status = 1
SELECT Relations.user_id, Users.name, Users.email, Users.pic_url FROM Relations
INNER JOIN Users
ON Relations.user_id = Users.id
WHERE Relations.friend_id = $user_id
AND Relations.status = 1
Your problem is that you're selecting friend_id which means that if you execute your query as user B, the friend_id
would be 2 - user B. (see your table example)
You should take a look to this question for more information about performance.