How do I display the correct number of comments for my status system?

Question

I'm creating a system where users can write their current status and other users can comment.

If anyone has commented on a user's status the user will get notified, but I'm having an issue coding this behavior.

My desired behavior is this: if i have one comment on my status (by one user, of course):

echo "user has commented your status"

If I have two comments in 2 of my statuses (and the both are from same user):

"user has commented your status" 
"user has commented your status" 

(the text should be printed twice, each for one status)

If I have 3 comments in my 2 statuses (where the 1st status has 1 comment from one user, and the second has 2 by different users)

echo "More than one users has commented your status"
echo "user has commented your status"

So if I have 2 comments in 1 status (where both comments are different user and NOT the same)

echo "More than one users has commented your status"

To summarize: my system should only echo one time for EACH status notification. If its one comment from one user, or more comment in one status, from one user, you will get:

echo "user has commented your status"

but if comments come from different users in one status, you will get:

echo "more than one has commented your status

When a user comments, it also saves in users_msgs a row, with:

uID | nData | icon

• uID contains the status creator's id (not the id of the user who commented)
• icon contains the commenting user's id.
• nData contains the status id.

(please do not comment on the names of the columns)

How can I do this? I don't know where to start.

The only thing that I know should be done is that in the query should be uID = '$USER' (where $USER is the id of the signed-in user). But how do I check if there's more than one user that has commented per status id (nData), and if not show a single message of user has commented on your status.

If something's unclear please comment and I'll update my question.

Answer 1

For each status change of each user you should have "not read comments count" variable. For example, user "USER" has changed his status twice:

user_id  status  not_read_comments_count
USER     BUSY    0
USER     AVAIL   0

And people has left some comments on the status changes:

user_id  status  not_read_comments_count
USER     BUSY    2
USER     AVAIL   1

When "USER" comes back to site and reads his profile, your program reads "not_read_comments_count" for each status change and yields:

"You have 2 comments on your status" "You have 1 comment on yout status"

and right after yielding it resets all "not_read_comments_count" to 0:

user_id  status  not_read_comments_count
USER     BUSY    0
USER     AVAIL   0

Answer 2

Just change how you're querying. What you want to find is the number of users that have commented on a particular status and the number of comments. Based on those two pieces of information you can determine what to print.

If you use GROUP BY grouping statements you can aggregate data about each status message. You should group by the id of the status message, this is nData. Then all you need to do is count the number of unique users (that's unique values of icon) and also the raw number of comments, which can be achieved using COUNT(*).

 SELECT uID, nData, 
     COUNT(DISTINCT icon) as num_commenting_users, 
     COUNT(*) as num_comments 
 FROM users_msgs WHERE uID=$USER GROUP BY nData

Now num_commenting_users will tell you the number of unique users that have commented on status with id nData, and num_comments will tell you how many comments have been made on status with id nData.

For each row returned by this query, you can then do a simple check, if there are more than 1 unique commenting users, print "more than one has commented your status", else print "user has commented your status":

$query = mysql_query(...);
while($row = $mysql_fetch_assoc($query)){
    if($row['num_commenting_users'] > 1){
        echo "more than one has commented your status";
    }else{
        // we know there was at least one comment made, and
        // even if many comments were made, they were all made
        // by the same user (since there was exactly 1 num_commenting_users)
        echo "user has commented your status";
    }
}