CODEWITHSUNDEEP
phpmysqldatabasesocial-networking
Windom Earle
Windom Earle

Reputation: 668

Listing all friends and joining to the users table

I have a database named 'friends' and it has columns 'user_id' and 'friend_id'. user_id is the invitor and friend_id the recipient. Please note that when a friendship is created I'm NOT making 2 records in the database like 1,2 and 2,1.

How to list all the friends of mine, considering that my users.user_id can vary between friends.friend_id and friends.user_id in the 'friends' table. Also how to join the query to the 'users' table to get the names of all my friends.

Upvotes: 3

Views: 1176

Answers (3)

Arc
Arc

Reputation: 11306

I think it would probably be easiest to ensure that the friends table always has the lower ID in the user_id column and the higher one in the other.
This way, you do not need to check twice, thus achieving better performance.

However, this approach requires you to modify your friend-adding code and assumes that user IDs never change (or you have to update your IDs in the table).

Otherwise, use UNION queries like Joe said or to join using an OR condition (e.g. JOIN ... ON u.user_id = friends.user_id OR u.user_id = friends.friend_id) or IN (JOIN ... ON u.user_id IN (friends.user_id, friends.friend_id)), which might be expensive operations (use EXPLAIN and benchmarks to find out).

Upvotes: 0

Michiel Pater
Michiel Pater

Reputation: 23053

You should make a Join query to get the best performance. Try something like this:

$friends_result = mysql_query("
    SELECT
        users.`name` AS name
    FROM
        friends
    LEFT JOIN
        users
    ON
        users.`id` = friends.`user_id`
    OR
        users.`id` = friends.`friend_id`
    AND
        users.`id` != '" . $user_id . "'
    WHERE
        friends.`user_id` = '" . $user_id . "'
    OR
        friends.`friend_id` = '" . $user_id . "'");

echo "<strong>My friends:</strong><br />";

while($friends_array = mysql_fetch_array($friends_result))
{
    echo $friends_array['name'] . "<br />";
}

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

One option would be a union of two queries:

select u.name
    from friends f
        inner join users u
            on f.user_id = u.user_id
    where f.friend_id = @YourID
union
select u.name
    from friends f
        inner join users u
            on f.friend_id = u.user_id
    where f.user_id = @YourID

Upvotes: 3

Related Questions