Friendship System Sql Structure & Query

Question

Hello.

I am coding a friendship system and it has two tables.

members

• id
• username
• password

friends

• id
• user_id
• friend_id
• status

Let's say that i want a query that can select the friends IDs of the member $userId how possible to make this in one query?

I found a solution which is to make 2 queries. The fist selects the friends WHERE user_id = $userId AND the second selects friends WHERE friend_id = $userId and then MIX them in one array. If there is no other solution I'm going to use it.

please any ideas for both the SQL structure & Queries?

Answer 1

You should be able to try using

SELECT  m.*
FROM    friends f INNER JOIN
     members m ON f.friend_id = m.user_id
WHERE   f.user_id = $userId

This will give you all the Friends details

To get BOTH have a look at

SELECT  DISTINCT CASE WHEN f.user_id = $userId then f.friend_id else f.user_id END CASE
FROM    friends f 
WHERE   f.user_id = $userId
OR   f.friend_id = $userId

Answer 2

Since you're asking for something simple like:

SELECT friend_id FROM friends WHERE user_id = id; [fill in the id]

I'll give you something fancier:

SELECT * FROM members AS m
WHERE m.id
IN (SELECT f.friend_id FROM friends AS f
  WHERE f.user_id = (SELECT pm.id FROM members AS pm
    WHERE pm.username = 'amindzx'));

Granted using a join over a sub-query would be better.

Also, there's no need for an id in the friends column, because only one relationship between a user_id and a friend_id should exist; these can both be described as the id columns in unison.

Answer 3

Use:

SELECT f.friend_id
  FROM FRIENDS f
 WHERE f.user_id = $user_id
UNION
SELECT t.user_id
  FROM FRIENDS t
 WHERE t.friend_id = $user_id

Using UNION will remove duplicates. UNION ALL would be faster, but it doesn't remove duplicates.

If you want to get the information for the friends from the MEMBERS table, use:

SELECT m.*
  FROM MEMBERS m
  JOIN (SELECT f.friend_id 'user_id'
          FROM FRIENDS f
         WHERE f.user_id = $user_id
        UNION
        SELECT t.user_id
          FROM FRIENDS t
         WHERE t.friend_id = $user_id) x ON x.user_id = m.id

BTW: I hope you're using mysql_escape_string on the variables, otherwise you risk SQL injection attacks:

Answer 4

Why not inserting 2 rows for 1 friendship. For example:

Let's say we have 2 user will become friends

User_id : 1 & Friend_id : 2

insert into friends (user_id, friend_id, status) values (1,2,0)    
insert into friends (user_id, friend_id, status) values (2,1,0)

so you can select easily by simple select query.

Also it will ease the pain for your likely next question "How to find Mutual Friends".