I have user discussion forums I coded in php/mysql, I am wanting to know how the big name forums can make it show you which topics have new posts in them, usually by changing an icon image next to the thread without using hardly any resources?

The simplest way is to track the last time someone was logged in. When they come back to visit, everything which has been updated since then is obviously "new".

This has some problems though, since logging out effectively marks all items as read.

The only other way I could think to do it would be to maintain a table containing all the threads and the latest post in that thread which each user has seen.

user_id   thread_id   post_id
      1           5        15
      1           6        19

With that information, if there is a post in thread #5 which has an ID larger than 15, then you know there's unread posts there. Update this table only with the post_id of the latest post on that page. This means if there's 3 pages of new posts, and the user only views the first, it'll still know there's unread posts.

As nickf said above except that the threads the user has actually visited is tracked. so anything the user hasn't visited is considered new for that visitor. for finer grain control any threads created before the user registered are ignored and possibly any threads not visited within a period of time are ignored. this would prevent every unvisited thread as becoming a new thread for them.

Of course there are many ways to skin a cat and depending on what the forum creators wanted the above can be changed to suit

DC

You could log the last time they selected that topic and then see if a post has a later time-stamp then their last "click" on the thread.

You could make a special table in your database with columns like USER_ID and THREAD_ID and with appropriate constraints to your USER and THREAD tables and a primary key containing USER and THREAD IDs.

Now when somebody opens a thread, you just insert that USER-THREAD-PAIR into that special table.

In your thread listings you can now simply outer-join that table on to what ever suits you use there. if your new table contains NULL on any particular spot, that thread is unread. This will enable lists like:

• All Threads with "unread" marker
• All unread threads
• Threads read by user XY

If you add a date column to this table, you can do even more interesting stuff.

Just keep an eye on your keys and indexes to prevent too heavy negative performance impacts. Try to read from the USER-THREAD-table only by joining it into your existing queries. That will work much faster than executing individual queries all the time.

You could have a table that gets an insert whenever a thread gets read, if the user reading it hasn't already. Then when someone adds to the thread you can delete all entries in the table for that thread, thus making it unread for all users.

The table structure would be something like

forum_id thread_id user_id

With the optional extra has_read_id for your primary key, with the other fields making a composite key.