CODEWITHSUNDEEP
mysqlsql
Khrisna Gunanasurya
Khrisna Gunanasurya

Reputation: 745

MySQL: User online status [HELP]

I already make a user online status for friends only

SELECT * FROM users 
WHERE
    (id_user IN 
        (SELECT CASE 
            WHEN friend_one = '{$id}' 
                THEN friend_two 
            WHEN friend_two = '{$id}' 
                THEN friend_one 
            END 
        FROM friends 
        WHERE status = '1')
    ) AND (status = '1' OR ($date - last_login) <= 300)

And it works, this mysql only show the list of friends login status.

But the problem is when i try to make the another user login status, that only shows the list of another people that logged in but not in our friend list.

SELECT * FROM users 
WHERE
    (id_user NOT IN 
        (SELECT CASE 
            WHEN friend_one = '{$id}' 
                THEN friend_two 
            WHEN friend_two = '{$id}' 
                THEN friend_one 
            END 
        FROM friends 
        WHERE status = '1')
    ) AND (status = '1' OR ($date - last_login) <= 300)

So this is the result i want

Logged as: khrisna
Friend list: Ben, Alpha, Ralph
All User: Ben, Alpha, John, Mark, Zayn

Online friend list: Ben, Alpha

Another online user: John, Mark, Zayn

Can somebody help me to reach the result i want?

UPDATE

users table

enter image description here

friends table

enter image description here

I want to make something like this for the result

Online user : Khrisna Gunanasurya // logined account

Friend list online : Alpha, John // online friends of logined account

Other user online: Mark, Ralph // other user that login and not friend of logined account

Upvotes: 0

Views: 464

Answers (1)

peterm
peterm

Reputation: 92785

One way to do it:

Get all friends that are online 

SELECT *
  FROM
(
  SELECT CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END friend
    FROM friends
   WHERE ? IN(friend_one, friend_two)
     AND status = 1
) f JOIN users u
    ON f.friend = u.id_user
 WHERE status = 1 OR last_login >= UNIX_TIMESTAMP() - 300;

Get all non friends that are online

SELECT *
  FROM users u 
 WHERE id_user <> ?
   AND NOT EXISTS
(
  SELECT *
    FROM friends
   WHERE ? IN(friend_one, friend_two)
     AND status = 1
     AND u.id_user = CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END
)
   AND (status = 1 OR last_login >= UNIX_TIMESTAMP() - 300);

Question mark represents a value of an Id of logged in user (e.g. Khrisna Gunanasurya)

Here is SQLFiddle demo

Your php code might look like 

$sql = "SELECT *
  FROM
(
  SELECT CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END friend
    FROM friends
   WHERE ? IN(friend_one, friend_two)
     AND status = 1
) f JOIN users u
    ON f.friend = u.id_user
 WHERE status = 1 OR last_login >= UNIX_TIMESTAMP() - 300";

$stmt = $pdo->prepare($sql);
$stmt->execute(array_fill(0, 2, $id));

Upvotes: 1

Related Questions