New posts notification with PHP and MySQL

Question

I know how to implement things in PHP and MySQL, but now i have a little thinking problem.

There's a forum-website. Every user could post a question (like stackoverflow). So how could it be done that when the user returns (after 2 days, or something) that he sees (a message, or alert...) that he has new posts on his thread?

Do you know what i mean? I can't put that all into a database, I think that's a little bit server-heavy??

Thanks.

Answer 1

If I recall correctly, here's how Simple Machines Forum handles it.

Each user has a record for each thread that they've clicked on. The record stores the highest message ID for that thread at the time it was clicked.

Since message IDs are auto incrementing integers, any new message will have a message ID larger than the previous.

So, when the user views the list of threads, SMF sees if the current highest message ID in the thread is larger than the one stored in the user's thread record. If it's larger, there are new records, and the thread gets a "new messages" icon. Otherwise, the thread gets the "no new messages icon". Also, if there is no thread record, then the user never visited the thread, and it gets a "new messages" icon.

When the user actually clicks on the thread, the thread record is updated with the highest message ID again.

Answer 2

You should store the lastActiveTime of the user on the site. Every time the user visits a thread or a page, update this value for them. The next time they return, do a query for all posts created after the lastActiveTime and present a list of links.

This means you only need to add one field to the user table, and nothing more. This new field contains a simple date from which you can determine the freshness of certain posts.

Answer 3

You can put that all into a database...

Where else would it all go?

Magic Land? ^_^

Just store it all in the database with some sort of time element added to each row.