Php mysql未读用户的消息

Question

I am using a message system inside my application and i want to get the number of unread messages in his profile the schema is as follows

Users table     message

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

flag where is use to determine whether a user has read a message or not . flag=0 user has not read a message and 1 as read

how to get the number of unread message of a particular user

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

Answer 1

You're almost there, try this:

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;

Also you can get read count by following:

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;