PHP "You have () new comments on your clip", how?

I want to do a function to my users, so on index.php there is e.g:

You have 2 new comments on your clip

How should i do this? I mean i want ideas to do this the easiest way. Table for the videos is member_videos, and tables for the comment is member_videocomments, a comment inside the table is connected by their "videoID", which is the id of the column in member_videos.

Should i do the classic, making a field开发者_Go百科, which all is 0, until it has been seen by the user its 1 or what should i do.

---

One alternative to the unread flag on each comment is a last_read_comments timestamp on the user record. Whenever they read their new comments, update the timestamp to the current time. When they land on your homepage, query for all comments newer than that timestamp value.

---

Why don't you just do a check when you load the video against the comments table that says something like

SELECT COUNT(*) FROM member_videocomments WHERE videoID = videoIDLoaded;

Then get the result set back as an integer and check if that integer is equal to 0, if it is then display 0, else query the database for the comments get the result set back and display all the comments on the page however you like.

---

Just update a field in the table member_videocomments, something like readbyuser. Leave it at zero until the user views that specific comment.

$result = mysql_query("SELECT id FROM member_videocomments WHERE !readbyuser");
$num_rows = mysql_num_rows($result); 

---

You've given the answer yourself. Just mark the comments when they are actually being displayed to the user. Assuming new comments are not viewed all at once but instead per video clip, I don't think the timestamp approach would be a good solution, because when you update the timestamp once the user has opened one of their newly commented videos, you will lose track of other new comments.

---

I have a similar situation, and I am using a is_read flag. When comments are added to the database, is_read is set to 0. When a user logs in, I check for any unread comments (it's here that I grab the # of unread comments so I can display it). Then when they view them, I grab the IDs of the comments and run a query to set their is_read to 1.

Matthew's timestamp solution is also good, so really it's up to what you feel more comfortable with.