Writing a forum - keeping track of what threads u've seen?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
leecher
Forum Newbie
Posts: 2
Joined: Sat Feb 13, 2010 8:45 am

Writing a forum - keeping track of what threads u've seen?

Post 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!
AntonioCS
Forum Newbie
Posts: 11
Joined: Tue Jan 15, 2008 12:31 pm

Re: Writing a forum - keeping track of what threads u've seen?

Post by AntonioCS »

Why don't you go and see how it is done in phpbb or smf? They are both open source!

Good luck :)
User avatar
PHPHorizons
Forum Contributor
Posts: 175
Joined: Mon Sep 14, 2009 11:38 pm

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
leecher
Forum Newbie
Posts: 2
Joined: Sat Feb 13, 2010 8:45 am

Re: Writing a forum - keeping track of what threads u've seen?

Post by leecher »

Thanks for the replays!

I think I got some ideas up.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Writing a forum - keeping track of what threads u've seen?

Post 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)
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Writing a forum - keeping track of what threads u've seen?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Writing a forum - keeping track of what threads u've seen?

Post by Eran »

I see now. Frankly I'm not sure which approach is more complicated but they're definitely both valid.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Writing a forum - keeping track of what threads u've seen?

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply