Defining unread items in a forum

Question

Using PHP and MySQL, I have a forum system that I am trying to create. I want to know how I can configure it so that when a user reads a forum entry, it appears as a read JUST for that user, no matter what forum they are on until someone else sends messages to it.

Currently, for each thread, I have a table with a PostID, and I have a user ID associated with ThreadID, the actual post (as text), and then the date / time at which it was sent.

For the list of threads in each forum, there is threadID (primary key), ThreadName, ForumID that belongs, NumPosts, NumViews, LastPostDateTime and CreateDateTime. Any help?

Answer 1

It can be stored in another table UserID, threadID, LastReadDateTime when the user reads this stream.

if (LastPostDateTime > LastReadDateTime) you got an unread post. 

Unfortunately, you have a lot of overhead, with each reading you will write.

Answer 2

The traditional solution is a join table something like rows:

 CREATE TABLE topicviews ( userid INTEGER NOT NULL, topicid INTEGER NOT NULL, lastread TIMESTAMP NOT NULL, PRIMARY KEY (userid, topicid), FOREIGN KEY (userid) REFERENCES users(id), FOREIGN KEY (topicid) REFERENCES topics(id) ); 

with lastread is updated every time you read the topic. When a topic list is displayed, if topic.lastupdated is> topicviews.lastread, new messages appear.

The traditional solution is garbage and kill your database! Do not do this!

The first problem is that writing to each topic view will soon bring the database server to its knees in a busy forum, especially on MyISAM tables that only have table-level locks. (Do not use MyISAM tables, use InnoDB for everything except full-text search).

You can improve this situation a little only by bothering to write after the last reading, when there are new posts in this thread. If topic.lastupdated <topicviews.lastread you have nothing to get by updating the value. However, in a heavily used forum, this can be a burden.

The second problem is a combinatorial explosion. One line for each user in the topic will be added soon: a total of a thousand users and a thousand topics, and you have potentially a million lines to view!

You can improve the situation a bit by limiting the number of topics remembered for each user. For example, you can remove any topic from the presentation table when it is older than a certain age, and just assume that all old topics are “read”. Usually this is a cleaning task that should be performed in the background.

Other, less intense approaches include:

• saving only one last time on the forum
• only saving one last time for each user throughout the site, which will be displayed as “new” only updating, since the user's previous visit (session)
• Do not store all the latest information, but including the most recent updates in the URL itself. If the user's browser has recently viewed a topic, they will remember the URL and mark it as having visited. You can then use CSS to search for visited links as "topics without new posts."

Answer 3

The general ideas here are correct, but they have missed some obvious solutions to the scalability problem.

@bobince: The second problem is a combinatorial explosion. One line for each user in the topic will be added soon: a total of a thousand users and a thousand topics, and you have potentially a million lines to view!

You do not need to save the entry in the "topicviews" table if someone else has not viewed this stream. You simply display a subject with unread messages if null is returned OR the time last_read is <last_post. This will reduce the number of “millions” of lines, perhaps an order of magnitude.

@gortok: There are many ways to do this, but each one grows exponentially more when the user visits the site.

In this case, you archive the forum after n-messages or n-weeks and when you block, you clear the "topicviews" table.

My first sentence is obvious and has no shortage. My second one is reducing usability on archived topics, but it's a small price to pay for a quick forum. Slow forums just hurt to read and publish.

But honestly? You probably won't have to worry about scalability. Even a million lines are really not many.

Answer 4

There is no easy way to do this. There are many ways to do this, but each one grows exponentially more as the user visits the site. The best you can do and still maintain performance is to timestamp and mark any forums that have been updated since your last visit as “unread”.

Answer 5

You can simply use the functionality of a custom browser and add the last postid to a thread link like this: "thread.php? Id = 12 & lastpost = 122"

Using: visited in your CSS, you can display messages that the user has already read, other than those that he has not read.

Answer 6

Binsey has a lot of good suggestions. A few more possible optimizations:

Write a new "is it new?" information in memcached and in the MySQL "ARCHIVE" table. A batch job can update a "real" table.

For each user, save the "read all until $ date" flag (for cases when the "mark all read" button is clicked).

When something new is published, clear all the “it was read” flags, which keeps the number of “flags” down, and the table can be simple (topic_id, user_id) - no timestamps.

Answer 7

It uses the users browser functionality and adds the last message identifier to the stream link. After use: visited in CSS, you can display the entire stream that was not read by the user.