用户的php mysql未读消息

Question

我在我的应用程序中使用了一个消息系统，我想在他的个人资料中获取未读消息的数量，架构如下

Users table     message

user_id         message_id 
......          sender_id
                receiver id
                text 
                flag

标志 where 用于确定用户是否已阅读消息。 flag=0 用户未读消息，1 已读

如何获取特定用户的未读消息数

SELECT message_id, count(*)
FROM message
WHERE flag=0
GROUP BY receiver_id;

Answer 1

你快到了，试试这个:

SELECT receiver_id, count(*) as unread_cnt
FROM message
WHERE flag = 0
AND receiver_id = '$particular_user_id'  // If you want all users, you can comment out this line.
GROUP BY receiver_id;

您还可以通过以下方式获取阅读次数:

SELECT
    receiver_id,
    COUNT(CASE WHEN flag = 0 THEN receiver_id END) as unread_cnt,
    COUNT(CASE WHEN flag = 1 THEN receiver_id END) as read_cnt
FROM message
WHERE receiver_id = '$particular_user_id' // If you want all users, you can comment out this line.
GROUP BY receiver_id;