MySQL:用户在线状态[帮助]

Question

我已经为 friend 设置了用户在线状态

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)

它有效，这个mysql只显示好友登录状态列表。

但问题是，当我尝试使另一个用户登录状态时，只显示其他登录者的列表，但不在我们的 friend 列表中。

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)

这就是我想要的结果

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

有人可以帮助我达到我想要的结果吗？

<小时/>

更新

用户表

friend 表

<小时/>

我想做这样的结果

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

Answer 1

一种方法:

获取所有在线好友

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;

获取所有在线的非好友

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);

问号代表登录用户的 ID 值(例如 Khrisna Gunanasurya)

这里是SQLFiddle 演示

<小时/>

你的 php 代码可能看起来像

$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));