mod geek mod geek - 4 months ago 17
PHP Question

Php mysql unread message of a user

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

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;