Page 1 of 1
Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 13, 2010 8:54 am
by leecher
First post/topic here so hello to all of you.
I've been wondering about something and I just cant figure out how to make it.
At i'm writing a forum and i'm kindda done with it, but my problem is that I can think of a way for users to keep track of what threads they've opened and/or if there is new posts in this thread.
One way for doing that is writing the information of what u've read in a mysql table, but if you have 10000 accouns how possible is to keep track of what hey have seen and what not. 10,000 users X 10,000 topics = 100,000,000 rows ... come on -.-.
Another way is to write it in the $_SESSION or cookie, but sessions expire and cookies can be deleted so aint an option.
I dont need code help, but theory help on this thing. Can anybody try explaining how this is done on other forums ( phpBB,ipb, and so on). Thanks in advance!
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sun Feb 14, 2010 10:46 am
by AntonioCS
Why don't you go and see how it is done in phpbb or smf? They are both open source!
Good luck

Re: Writing a forum - keeping track of what threads u've seen?
Posted: Mon Feb 15, 2010 8:19 am
by PHPHorizons
AntonioCS wrote:Why don't you go and see how it is done in phpbb or smf? They are both open source!
Good luck

As funny as that was, it's not at all helpful...
I've delved into both phpBB and SMF and I'll tell ya, it would probably take me a long time to be able to figure out how phpBB does this. As far as SMF is concerned, I have the opinion that it's code is not nearly as clean as phpBB (not that I think phpBB is clean, but that's where I put them in relation to each on the code cleanliness scale) and I wouldn't copy code from SMF... That's IMHO.
With that said, if someone knows how phpBB does this sort of thing, it'd probably only take a few minutes to outline it. What's wrong with asking for that in a PHP Theory and Design forum?
I've also delved into this are of programming but did not answer this post before because I only know how I did this, and not how anyone else would, nor do I know a "recommended way".
Anyways, you can have a field in the users table, or a viewed_posts_table where each user has one row in association with that viewed_posts_table, that has a comma separated value list of post id's that the user has viewed. Each time a user views a thread, add all the post id's to that field. Yes it'd be a big field.
I've theorized that this could be mitigated by having a viewed_threads_table. This would require a lot of synchronizing... I think...
One can also have a timestamp/datetime where any post before that time is considered read. So if a user selects "mark forum read", the viewed_posts_table and viewed_threads_table rows associated to the user are cleared, since all posts at this point are considered read.
Anyways, these are just some ideas I've tossed around in my head. I have since seen the error in my ways and realized that a forum designer I am not and I just use phpBB now. I am not trying to discourage you from doing this. I am only relating my own personal experience. I do wish you luck. I definitely had a lot of fun doing forum code, but am glad to not have the headache anymore as well.
Cheers and hope that helps.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Mon Feb 15, 2010 10:11 am
by josh
To sum it up, they don't (track every view). They simply track which ones you've seen during your current session. On the next session they count everything dated before your session as read... I think. Example: Goto last page (page 300) of the general forum. Every thread is marked read even though you didn't read those.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Mon Feb 15, 2010 1:24 pm
by John Cartwright
It just really isn't practical to accomplish this in a normalized database.. which is why it is usually done using the method Josh described.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Tue Feb 16, 2010 10:23 am
by xjake88x
You also asked this on phpfreaks and I think a good way to do this would be the same answer I provided there:
You have a "posts" table and a "users" table.
You need a table to create a relationship between posts and users and provide a date that relationship took place.
"read_posts" with
id, user_id, post_id, and datetime
When somebody reads a post, an entry is created/updated with the time, and corresponding IDs to link them together.
It is unread if the last post on that thread is AFTER the last date in your "read_posts" row, or if there is no read_posts row linking them together. Otherwise it is read.
Another thing to do would be associate a value in your "users" table called "read_before." This would enable users to "mark all posts as unread" without creating 50,000 new rows. Just set the current date in "read_before." Any posts taking place before that date are considered "read."
Hope that makes sense. That's my crack at it.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Thu Feb 18, 2010 11:55 am
by leecher
Thanks for the replays!
I think I got some ideas up.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Thu Feb 18, 2010 12:11 pm
by Eran
Against popular opinion, I do think the normalized approach is the way to do it (similar to what xjake suggested - only you don't need an id field), at least until you run into performance issues and then you could denormalize. It should work well for small-to-medium sizes forums and even beyond if you prun older records, depending on how fast they're accumulated.
If you consider your original numbers, there is absolutely no way all users in the a forum have read all threads (you should be keeping a record only for a read thread by a user, obviously). In most forums, there are maybe 4-5% of the users who are very active and may have read 30%-40% of the threads, and that's being very generous. About 20% are returning users who read maybe 10% of the threads and the rest haven't posted more than a couple of times and do not return to read more threads.
So, crunching your numbers again, for 10,000 user forum with 10,000 threads, you get -
5% active of 10,000 users = 500
40% of 10,000 threads = 4,000
Threads read by active users = 500 x 4,000 = 2,000,000
20% moderately active of 10,000 users = 2,000
10% of 10,000 threads = 1,000
Threads read by semi-active users = 2,000 x 1,000 = 1,000,000
The rest - 7,500 users who read ~10 threads = another 75,000 threads read.
Total - 3,075,000 read threads, and not 100,000,000 - and that's without prunning.
MySQL can handle those amounts with relative ease for well indexed tables. (It can handle 100,000,000 rows too, you just have to be more careful with your indexes and queries)
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 20, 2010 6:19 am
by kaisellgren
You can also achieve this without using databases for tracking anything. Just use CSS. If the browser has the URL of the topic in its history, use CSS's :visited to make it clear to the user. Note, you would need to append the latest post of the topic into the URI like "topics.php?topic=1&lastpost=123". It's pretty neat, but the feature depends on the browser's history.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 20, 2010 5:46 pm
by josh
I would still just use the browser's cookie and the "last visited timestamp" Why have a 10 million row table when a 10kb cookie and 1kb timestamp work? Do the simplest thing possible. Theres a *huge* difference in having a 100,000 table a 10,000,000 table (although as pytrin pointed out with enough fidgeting/tuning queries & settings you could eventually get them to perform on par with each other. Why muddle with 1,000s of lines of code trying to deal with huge numbers though?)
The CSS solution is interesting but everytime I clear history all threads would be marked read again. Would work good for the links on your company website, for a full fledged forum I wouldn't think so.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 20, 2010 5:53 pm
by Eran
The problem with all those browsers-based solutions you're offering is that if I clear my cookies or log-in using a different browser / computer, I don't have any access to my history (in the case of cookie clearing, I'd lose it permanently). Personally, I'd prefer it to be always available.
phpBB does have my history regardless of which browser/computer I log-in from so it definitely persists the data in the database in some form.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 20, 2010 8:01 pm
by josh
Thats not my point. My point is it stores timestamps, not a 10 million row table:
Code: Select all
CREATE TABLE IF NOT EXISTS `forums_track` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`forum_id` mediumint(8) unsigned NOT NULL default '0',
`mark_time` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`,`forum_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Code: Select all
CREATE TABLE IF NOT EXISTS `topics_track` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`topic_id` mediumint(8) unsigned NOT NULL default '0',
`forum_id` mediumint(8) unsigned NOT NULL default '0',
`mark_time` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`,`topic_id`),
KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
The topics_track table would be pruned
very frequently way before it reached 10 million rows.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sat Feb 20, 2010 8:13 pm
by Eran
I see now. Frankly I'm not sure which approach is more complicated but they're definitely both valid.
Re: Writing a forum - keeping track of what threads u've seen?
Posted: Sun Feb 21, 2010 4:02 am
by VladSun
josh wrote:To sum it up, they don't (track every view). They simply track which ones you've seen during your current session. On the next session they count everything dated before your session as read... I think. Example: Goto last page (page 300) of the general forum. Every thread is marked read even though you didn't read those.
I don't think that logout would perform "mark-all-as-read". It doesn't in this forum.
I think that there are two options for new users - all posts before his/her signing up are marked 1) as read 2) as unread

The second one insists that the user must hit the "mark-as-read" button to clear them. The first one looks better to me.
As for "marking" - I do agree the timestamp approach is better. I'm not sure why you need cookies, but be careful with these *10K* cookies - usually a 4K sized cookies are permitted by default
