I'm trying to show relation between two users. I want to insert only one row to Relations table (see structure below). But my script is showing that user B is friend of himself - that's not right.
This is a structure of my database (with examples):
Users
id|email|password|name|pic_url|friend_count
1 |a@.. |aaaaaaaa|A |http...|1
2 |b@...|bbbbbbbb|B |http...|0
Relations
id|user_id|friend_id|status(0 and 1)
1 |1 |2 |1(if accepted)
What am I doing wrong? Here's my query:
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
OR Relations.friend_id = $user_id
AND Relations.status = 1
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.