0

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
Genhis
  • 1,484
  • 3
  • 27
  • 29

1 Answers1

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.

Community
  • 1
  • 1
Genhis
  • 1,484
  • 3
  • 27
  • 29
  • thank you for your efforts sir but i am about in more concerned that will be the right way? if i have many many of tables wouldnt it be slow –  Jul 18 '15 at 15:44
  • @emanual Tell me, how many users do you have? If it's more than one hundred thousand, my solution probably wouldn't be the best. – Genhis Jul 18 '15 at 15:49
  • i think there wouldnt be probably more than one hundred thousands i think but would break the normalization where as example is good too so asking you about these things as i think you know more than me –  Jul 18 '15 at 15:53
  • @emanual If you really want to insert only one row, you would need to execute two queries - see my updated answer. – Genhis Jul 18 '15 at 16:21
  • but making two queries would solve one problem and makes another problem too how to put the object in loop i was putting 1st object to loop now how to put ur second query in loop? –  Jul 18 '15 at 17:07
  • @emanual I can't tell you that until I see your loop, but as you have two queries, you should have two loops. – Genhis Jul 18 '15 at 17:12
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83623/discussion-between-emanual-and-genhis). –  Jul 18 '15 at 17:14