show unread forum posts

[jwk811]

i made my own forum now when someone logs on there will be a picture showing if you have not read this post yet. i have some ideas of how i could do it, like have a database table to record all viewed  posts by that person. is that the only way to do it? im not sure if this is the best way bcuz it would make a HUGEEE table like 100s if not 1000s PER PERSON registered

[ignace]

Keep it simple Store the date the user last visited. When he returns to your forum you mark every topic unread if the creation date of the topic is greater then the date he last visited your forum.

[Daniel0]

bcuz it would make a HUGEEE table like 100s if not 1000s PER PERSON registered

 

Indeed it will:

mysql> SELECT COUNT(*) FROM phpfreaks_smf.smf_log_topics;
+----------+
| COUNT(*) |
+----------+
|  1563332 |
+----------+

 

 

However, what you can do is to only store those records for say 30 days and then just automatically mark topics that haven't been responded to for 30 days as read. Of course, if you want to store the complete view history, then you need a row for each topic*user.

[gevans]

Also this is the sort of work mysql does. Your average application isn't even scratching the surface of a mysql installation. If you get up to the 1000's in the table you might actually start taking advantage of what mysql has to offer

[Ken2k7]

bcuz it would make a HUGEEE table like 100s if not 1000s PER PERSON registered

 

Indeed it will:

mysql> SELECT COUNT(*) FROM phpfreaks_smf.smf_log_topics;
+----------+
| COUNT(*) |
+----------+
|  1563332 |
+----------+

 

 

However, what you can do is to only store those records for say 30 days and then just automatically mark topics that haven't been responded to for 30 days as read. Of course, if you want to store the complete view history, then you need a row for each topic*user.

Daniel0, you should have also included the time it took MySQL to count all those rows.

[Daniel0]

About half a second.

[dmcke5]

Hi guys i'm new here and im having the same problem. Ive spent the last few weeks writing a simple messageboard/forum type thing for my website. So far i haven't had any problems but i have users requesting that i make it so it will show notification on threads with new replies in them. I have thought about the storing time and date of each user login but from what i can tell, that doesn't solve the problem. Because, if you were to login without reading any of the unread posts, then logout, when you next login all of those posts will be marked as read because they were posted before the users last login.

 

Please help

 

Cheers, Daniel

[PFMaBiSmAd]

One word - bookmark

 

Image how a bookmark works when you are reading a book (when applied to a forum, the book is continually having pages/posts added on to the end.)

 

You save the bookmark datetime in the user table for each user. This has nothing to do with log in/log out.

 

When someone registers, you set the bookmark to the current datetime (you could set it to some past amount of time but that is probably not necessary for a forum.)

 

When they visit (requires that they be logged in so that you can find their record in the user table that holds their bookmark datetime), you search for all the posts that are newer then their current bookmark datetime and add the id of these posts to an "unread" table along with that visitor's id. You then set that visitor's bookmark datatime to be that of the latest post added to the "unread" table.

 

As the visitor views/reads those posts, his record in the "unread" table for that post is removed.

 

If he clicks on a link to mark a post as being "unread" the id of that post along with his id is added to the "unread" table.

 

If he clicks on a link to mark all posts as being read, all his rows in the "unread" table that are older than his current bookmark datetime are deleted.

 

If he clicks on a link to show posts since last visit or refreshes the page or after he clicks on any of the links mentioned above, the search for posts newer than his current bookmark datatime are added to the "unread" table and his bookmark datatime is set to the datatime of the latest post just added to the table.

 

If a visitor does not visited for a long time, you can limit how far into the past you actually search for "unread" posts instead of unconditionally adding all the posts since his bookmark datetime. I.E. If his bookmark datetime is farter into the past than a limit you pick, just use the current datetime - that limit.

 

You can also clear out old rows (cron job/scheduled task) in the "unread" table using a limit you pick. If someone has visited but has not read posts that are older than a limit, periodically remove the rows older than that limit.

[Daniel0]

I think it'll be more efficient storing what they have read instead of what they haven't read. Imagine you want to store read/unread information for a month and assume anything older is just automatically marked as read. This forum, which is rather large, but not huge, had 25,117 posts in April. So, when a member registers you using your method you would also create that many new rows in the table. In April there was an average of 43.5 registrations each day, so that would mean 1,092,589.5 new rows to the unread table for just those people. The table would very quickly become enormous, and it's unlikely these people are going to read all those posts within a month. It would be much more efficient storing what they have read.

[PFMaBiSmAd]

When someone registers, you set the bookmark to the current datetime (you could set it to some past amount of time but that is probably not necessary for a forum.)

 

Just registering does not cause anything to be added to the "unread" table. Their "book" starts at the point of registration. The only time anything is added to the "unread" table is when the visitor browses to the site while logged in.

[dmcke5]

Great, thanks for the quick responses guys =) I can see how either way i do it, its going to be more difficult than i thought =P I had a thought of how i can do it but im not sure if it is going to work or not.... ill see if i can explain it.

 

In my replies mysql table i could add another row, which is a long string containing user names, seperated by spaces. When a post is created, it automatically contains all users usernames, except the person who posted it. When the message list is viewed by the specified user, the program searches to see if that persons name is in the list for that post. If so it then shows the unread icon, it will then remove that name from the list when the user reads that post.

 

Would this be hard to implement? Or is the other way going to be easier for me to set up?... Keeping in mind i will probably never have more than 100 users or so...(in fact, that's even probably optimistic)

[Daniel0]

This is how I think I would do it (sort of):

 

topics

topic_id (PK)

forum_id (FK -> forums)

updated_at (DATETIME - gets updated with NOW() when somebody posts in that topic, or the topic, or posts therein, are edited)

etc.

 

forums

forum_id (PK)

etc.

 

log_topics_read

user_id (FK -> users)

topic_id (FK -> users)

read_at (DATETIME)

(make user_id and topic_id a combined unique index)

 

users

user_id (PK)

etc.

 

 

 

Okay, so when a logged in user user_id=123 views a topic with topic_id=321 you would run the following query:

INSERT INTO log_topics_read (user_id, topic_id, read_at) VALUES (123, 321, NOW()) ON DUPLICATE read_at=NOW();

 

When you are viewing the topic listing you would simply left join the log_read table with the topics table (and possibly other tables if you wish). So when you've done that you can check if the user has read it. If the left joined read_at value is NULL, then the user hasn't read it at all, and if the timestamp is earlier than the topic.updated_at then there have been some sort of update to that topic since the user last viewed it.

 

If you are concerned about the size of the database then you can just automatically mark topics that haven't been updated for X days as read and prune log_read entries that are over X days old.

[dmcke5]

Ah ok, i think i've got the idea. Ill have to give it a try once all of my uni assignments are out of the way.

 

Thanks For the Help, Daniel