CODEWITHSUNDEEP
mysql
Frantisek
Frantisek

Reputation: 7693

Determine friends / friend requested / not friends using MySQL query

I have this MySQL table:

enter image description here

Let's imagine I am logged in as user 1 and I'm browsing the profile of user 2. As we are mutual friends (1 is friend with 2 and 2 is friend with 1), I need to echo "friends."

When I browse the profile of user 4, whose friendship I have requested, but they haven't accepted yet, I need to echo "friend requested."

When browsing any other user, I need to echo "add friend."

I can do the PHP part, I just don't know how to do the MySQL query.

Upvotes: 1

Views: 290

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

Are you expecting something like this? (just for the sql part, not the PHP :) )

SQLFIDDLE DEMO

SELECT DISTINCT a. user, 
                CASE 
                  WHEN a.user = b.friend 
                       AND a.friend = b.user THEN b.friend 
                  ELSE '' 
                end friends, 
                CASE 
                  WHEN a.user = b.friend 
                       AND a.friend <> b.user THEN a.friend 
                  ELSE '' 
                end friendreq, 
                CASE 
                  WHEN a.user <> b.friend 
                       AND a.friend <> b.user THEN a.friend 
                  ELSE '' 
                end addfriend 
FROM   demo a 
       LEFT JOIN demo b 
              ON a.id > b.id; 

| USER | FRIENDS | FRIENDREQ | ADDFRIEND |
------------------------------------------
|    1 |         |           |           |
|    2 |       2 |           |           |
|    1 |         |           |        16 |
|    1 |         |        16 |           |
|   16 |         |           |           |
|   16 |         |           |         1 |
|   16 |      16 |           |           |
|    1 |         |           |         4 |
|    1 |         |         4 |           |

Upvotes: 0

Martin.
Martin.

Reputation: 10539

SELECT COUNT(*) as `count`, `user` 
FROM `friends`
WHERE 
    (`user` = 1 AND `friend` = 16) OR 
    (`user` = 16 AND `friend` = 1)

When count is

  • 2 = mutual (you are friend with that person)
  • 1 and user is you - you requested that person friendship
  • 1 and user is that person - you are being requested for friendship by that person
  • 0 = no connection between you and that person

Upvotes: 2

t_motooka
t_motooka

Reputation: 565

select (case 
    when subquery.a > 0 and subquery.b > 0 then 'friends'
    when subquery.a > 0 then 'friend requested'
    else 'add friend' end) as "friend_string"
from (
    select
        (select count(*) from relationships where user = '$my_user_id' and friend = '$opponent_user_id') as a,
        (select count(*) from relationships where user = '$opponent_user_id' and friend = '$my_user_id') as b
) subquery

Please replace relationships with your table name, and $ variables with yours.

Upvotes: 0

Related Questions