Keeping track of read posts

[McCain]

Hi
How should I keep track of what posts a user have read in a forum? What I want to do is to present for the user a list of "Unread posts since your last visit". And when a user reads one of the posts it should be removed from the list.
The forum is stored in a MySQL database

Thanks,
//Tobbe McCain

[Acidic]

keep the date and time since the user last visited the forum. Then also keep the date and time the last post in a thread was done. hen do a search for posts that have been created after the last date and time the visitor visited the forum at.

[McCain]

Originally posted by McCain
And when a user reads one of the posts it should be removed from the list.
How would I do that part?

[visualAd]

Originally posted by Acidic
keep the date and time since the user last visited the forum. Then also keep the date and time the last post in a thread was done. hen do a search for posts that have been created after the last date and time the visitor visited the forum at.
This is how VBulliten keeps track of read posts.

If you want to do it on an individual post by post basis you will need to create a table which which will store thread views. Possibly with a structure similar to this:

ID - Uniquley identifies the view.
ThreadId - The ID of thee thread viewed.
UserId - The user ID of the thread viewed.

Each time a user views a thread an entry is created in the views table.

When the thead is updated a query will then be triggered which removes all records from the views table for that thread ID, effectivley marking it as read.

Although this is more complex and requires more overhead, it is definatly more accurate.

[CornedBee]

vBulleting goes beyond even this, in that it also remembers which post in each thread was the last a user viewed.

[McCain]

So the way to go is to have an extra table just for keeping track of what posts have been read by which user?

And that table should have these fields:
Id - Unique id of this view
UserId - User Id (User Name in my case)
ThreadId - The Id of the tread the user is viewing
PostId - The last post in the above thread at the time of clicking the link to view that thread

What would my SQL query look like to select all the threads with unread posts?
SELECT * WHERE ThreadId "doesn't exist in forumviews WHERE UserId = SESSION['usrname']"
What would the parts in "" translate to in the SQL language?

[Electroman]

The way i'd go about this is when the user logs on you generate a list for the user. It would work like:

Thread Table
ThreadID
....

User Table
UserID
.....


Link Table
LinkID
UserID
ThreadID
Read


Notice that when you generate this list you only add entries for threads that have had a new post since the last date logged in. You'd also just before creating the list run a query that will romove every entry with that userID in so to clean it before generating it :).

To take this further you could even change where I have Thread to post and it would be fine. would mean an extra stage before showing the threads but thats if you want it :).

[McCain]

Thanks for your responce Electroman, but I actually solved it using this table structure:
id primary auto_increment
usrname
categoryId
topicId
postId

Whenever a user views a topic I add it here (or if it's already in the table I update postId to be the id of the last post in that topic)
Then I just go trough all the topics and see if they're in the table, if they aren't I mark them as unread

[McCain]

The code for doing what I described in the post above this one is a big mess. If anyone can simplify it for me that would be great.
//If there are any categories (there should allways be, but just to be sure...)
if ($num > 0)
{
//Loop through all the categories
while ($forumRow = mysql_fetch_assoc($result))
{
//Select all the topics in the current category
$query = 'SELECT id FROM forumtopics WHERE categoryId = '. $forumRow['id'];
$res1 = mysql_query($query);
$num1 = mysql_num_rows($res1);

if ($num1 > 0)
{
//Loop through all the topics
while ($topicRow = mysql_fetch_assoc($res1))
{
$query = 'SELECT id FROM forumposts WHERE topicId = '. $topicRow['id'];
$res2 = mysql_query($query);
$num2 = mysql_num_rows($res2);

//Get the id of the last post in the topic
$lastPostId = mysql_result($res2, $num2 - 1, 'id');

//Get all rows in forumviews with that postId (Should only be 1 or 0 rows)
$query = 'SELECT id FROM forumviews WHERE postId = '. $lastPostId;
$res3 = mysql_query($query);
$num3 = mysql_num_rows($res3);

if ($num3 == 0)
{
//This post didn't exist in forumviews - so it must be new

//Add this categoryId to the list of categories with unread posts
$categories[] = $forumRow['id'];
}
}
}
}
}
That's the code I have on the first page of forum (where you see all the categories) Then I have the same code on the topics page, but that code i one SELECT shorter because I don't have to go trough all the different categories

[Electroman]

Posted by McCain
Thanks for your responce Electroman, but I actually solved it using this table structure:
id primary auto_increment
usrname
categoryId
topicId
postId

Whenever a user views a topic I add it here (or if it's already in the table I update postId to be the id of the last post in that topic)
Then I just go trough all the topics and see if they're in the table, if they aren't I mark them as unread Doesn't this mean that there will be an entry for every user-thread combination? Well for the one sviewed at least, or does this too reset each time they log in?

[McCain]

Yes, I'm affraid so... (one for every user-thread combination)

[McCain]

I can see how your method is way superior mine when it comes to generating that list I was talking about in my first post. But how would I use that for marking read/unread threads in the forum? Or should I keep what I have for that (as that's the only thing it does right now) and use your method for the list?

And also. That list you are talking about will grow as well, because I want to keep all unread post. So if the user is presented with a list of 10 new posts but only reads 5 of them, I want the other 5 to stay in the list and be displayed for the user the next time he/she loggs in

[Electroman]

Posted by McCain
I can see how your method is way superior mine when it comes to generating that list I was talking about in my first post. But how would I use that for marking read/unread threads in the forum? Or should I keep what I have for that (as that's the only thing it does right now) and use your method for the list? Well if you have a function that will return which category a ThreadID belongs to then you'd be able to use the table to find out if any of the entries in the table belong to a certain category then you will know whether or not it should be marked as read or not. The way its structured though would mean lots of queries (i.e. one for every entry).

Another option is if you add to the Structure of the Link table a field to hold what category it lies in, this would still use the function I just mentioned but it then only gets run once when the list is generated. Finding if a category is unread is then a simple query to see if any of the records have the category set to that one.


Just to mention, one issue I can see being a possible bug is if a thread was moved while a user is logged in. The only problem would be that the forum it was in is then affected not the new forum. This would be solved by updating all these entries when a thread gets moved which wouldn't be often.

[McCain]

If I do it they way you are suggesting I whould have one entry in the table for every user-'unread thread' combination, right?

[Electroman]

Posted by McCain
If I do it they way you are suggesting I whould have one entry in the table for every user-'unread thread' combination, right? Correct, mind this is only for the users currently logged in and for just the threads that they haven't read yet and the thread has been posted in since their last visit. It assumes all the posts that existed when the user last logged out have been read. As the user reads posts the list also goes down :).

There is still issues with it like when a new post is made while a user is on line it must somehow get added to the list. There is a way to get round this by adding an entry for the thread for every user online at the time, this would work but may appear to be a hack, not sure if I like it but that would work.

So there is problems doing it this way but as you saw there is complications both ways.

[McCain]

Originally posted by Electroman
Correct, mind this is only for the users currently logged in ...
Didn't think about that... That will decrease the entries in the table a lot... I will your way a try. I'll get back if I'm having problems implementing it

[visualAd]

Trying to keep track of all the threads which all the users have read will cause a lot of over head. I think it would be better to do it on a session by session basis.

By this I mean you would log the time of the users last visit and when they return all theads and posts created after the last login time will show as unread.

While the user is browsing the forums, you can then use something similar to what you are using now to keep track of which posts are read. When the users session times out, all their entries are then removed from the tracking table.

Sorry to butt in ;)

[Electroman]

Just to round up cos Visuals been convincing me on MSN that keeping track of read threads rather than unread would be better. He had a valid point which was that if a user hasn't been online for 6months then when they logged in it would take ages to generate a list of all unread threads. My method is rather simple (& suitable) as long as users aren't away for really long times, but if they are then the amount of new threads isn't that high.

So first of all lets get clear what we need:
* Be able to list all threads not read since last login.
* Be able to list threads in a forum and indicate which threads are unread.
* Be able to list forums and indicate if that forum has any unread threads.

Anything else that should be included before we think whats best?


So if you have a table that keeps track of all the threads you have read in this session, this must also record which post was the most recent when you read it (possible bug when that post gets deleted).
Now to create the unread list VisualAd gave this code (which I'm not sure if I fully understand): SELECT * FROM posts LEFT JOIN post_views ON posts.PostId=post_views.PostId WHERE UserId = '2' AND posts.Time = '3672332' AND posts.PostId IS NULL;
I can never remember what all the Joins mean :(.

[McCain]

Originally posted by Electroman
So first of all lets get clear what we need:
* Be able to list all threads not read since last login.
* Be able to list threads in a forum and indicate which threads are unread.
* Be able to list forums and indicate if that forum has any unread threads.

Anything else that should be included before we think whats best?

If a user doesn't read a thread while loged in I really would like that thread to show up as unread the next time the user logs in... Maybe we could limit it to just keep track of posts that's < 2 weeks old. Then we wouldn't have the problem with users that are away for looooong periods of time.

It would also be good if I could have a "go to first unread post"-button in threads with unread posts. But that isn't a great concern. If it causes to much hassle I won't bother.

Ohh, and visualAd, you are more than welcome to butt in ;)

[Electroman]

Posted by McCain
If a user doesn't read a thread while loged in I really would like that thread to show up as unread the next time the user logs in... Maybe we could limit it to just keep track of posts that's < 2 weeks old. Then we wouldn't have the problem with users that are away for looooong periods of time. Now this changes things.......if you do it this way than no matter which method you choose you'll be keeping entries for users even if they aren't logged in. You can have a maintanace script which is run every so often so that when a user hasn't been loged in for more than two weeks all entries to do with them are deleted though.

Table of Unread Method
When it was only users who are logged in it was fine because when someone makes a new post we just looped though all users who were logged in and add it to there list. Now we need to loop through every single user who has been logged in for the last 2weeks. Although there is a solution to this:
When a new post is made we loop though all users online like before but leave alone the rest.
When a user logs back in we then get there list, remove anything more than 2 weeks old. Then we add any posts that have been made within the last 2 weeks or the last time they were logged in (whichever is shorter).


Table of Read Method
This doesn't really effect this that much, just means that we delete entries after they are two weeks old instead of each time they log in.

[McCain]

Sorry, I thought I had made that clear earlier

[McCain]

Sorry for the late response but I have been buissy with other stuff, like switching to Linux and a lot of schoolwork. But now I'm ready to give this a shoot again.

I have read through this thread because I didn't remember all that was said. So now I have an idea as to how I could do this. Please share any thoughts you have about it.

Table structure (unread posts):
id auto_increment PRIMARY
userid
categoryid
threadid
postid
datetime
When I want to generate that list of unread posts I first delete all entries with a 'datetime' that's more than two weeks. Then I just select all entries with the same userid as the logged in user.
When I want to mark a category or thread as read/unread I just have to check in the table for a userid and categoryid/threadid combination. And when the user reads a post that post should be removed from the table.

The question is when to add posts to the table, and how to handle posts/threads/categories that changes while a user is logged in.

[Electroman]

Maybe it could be better to use a subscription kinda method? Like you'd have a table to keep track of what threads users are subscribed to then when a new post is made in a thread it will use that table to find out what users are subscribed to it and create an entry in an "unread" table so this list then ends up getting generated as posts are made rather than when they log on?


Subscription:

sub_id AUTO_INCREMENT PRIMARY
user_id
thread_id

Unread:

unread_id AUTO_INCREMENT PRIMARY
user_id
thread_id

Now when someone posts ina thread it will:
1. Use the querry: SELECT `user_id`
FROM `Subscription`
WHERE `thread_id` = ThreadID

This will give a list of users that are subscribed to that thread.

2. Now for each of those users and an entry in the Unread table. First check it a record already exists that is the same though.


Now when choosing an icon for a thread you will just see if any records exist which have the user's user_id and that thread_id.

When they view the thread you just delete any records that have that thread_id & that user_id.


A slight chnage would be needed for the individual posts though. Just ask me if you need a hand with that :).

[McCain]

I like the idea of subscribed threads for the list of new posts.
But if I use your idea that will also mean that when looking at the forum only subscribed categories with new posts will be marked as having new posts, right? Categories with new posts that the user isn't subscribed to won't be marked.
If that's how it is I don't think I will use that.
But thanks for keeping an open mind and comming with new ideas.

[Electroman]

I like the idea of subscribed threads for the list of new posts.
But if I use your idea that will also mean that when looking at the forum only subscribed categories with new posts will be marked as having new posts, right? Categories with new posts that the user isn't subscribed to won't be marked.
If that's how it is I don't think I will use that.
But thanks for keeping an open mind and comming with new ideas.
Good point but for the categories.

What you could do is run a querry to find out the date/time of the last post in that section. Then compare it with the last time the user logged in. This gives the style of the last version of vBulletin where to mark a forum read you had to select it from an option, this could be implemented by each forum having one of these date/times (like I said use your last logged in time) then when they mark it as read it replaces the time with the current.

A problem with this still is on the forum display it would only show subscribed threads as unread, all others would be unread. :(

[McCain]

The thing with just checking the date/time is that even if you don't read all the posts in a topic/thread while you're logged in that topic/thread will be marked as read the next time you log in as there are no new posts since you last logged out.

Or did I not understand what you ment?

[Electroman]

Yea the last line in my post I pointed out with that method all threads would be marked as unread unless you were subscribed to them :(.

Which method are you going for BTW? The one where you generate a "Read" list as you go? Or the one where you create a "Unread" list at the begining?

[McCain]

I think this is what I'll do:
1. Try to code the stuff I talked about in post #22 (list of unread posts).
2. Come back here with more detailed problems :D

Thanks for your help so far

[McCain]

I have now made it so that I keep track of when a user loggs out, so I get a date/time to match against the posts.

But I'm not very good at SQL, so I need a little help with translating this psuedo SQL into real SQL...$query = 'SELECT topic, lastdate FROM forumtopics WHERE lastdate > (SELECT timestmp FROM ppl WHERE usrname = "'. $_SESSION['usrname'] .'")';
topic is the thread title.
lastdate is the date and time of the last action in that topic/thread, like a new post or something.
timestmp is despite the name actually a DATETIME field in the database.
The rest should be pretty obvious

Thanks

[Electroman]

first thing to note is I'm not sure your allowed to do nested statements in mySQL. In oracle I was able to but when doing it for my site with mySQL it does allow it. So you'd have to do this as two querys bit like this:
$query = "SELECT `timestmp` ";
$query.= "FROM `ppl` ";
$query.= "WHERE `usrname` = '". $_SESSION['usrname'] ."' ";
$results = mysql_query($query);
$Result = mysql_fetch_array($results);

$query = "SELECT `topic`, `lastdate` ";
$query.= "FROM `forumtopics` "
$query.= "WHERE `lastdate` > ".$Result['timestmp']." ";
$results = mysql_query($query);


Unless your SQL server allows nested statements?

[McCain]

Yeah, I have two statements now, was just hopeing I could do it with one.
Technocrat's question (http://www.vbforums.com/showthread.php?t=318801) was also about nested statements and that was doable in one query.
I tried doing mine in a similar way but couldn't figure out how to do it.
I'm using mySQL.

[McCain]

Thanks for all your help Electroman. This part of my forums are working like I want it now.
Now I can move on to add moderator functionality, like moving threads and stuff. Butt if I need help with that I'll make a new thread.

Thanks again Electroman (and you too visualAd)