Grab unread posts.

[JasonLewis]

Okay, I'm muddling over the best way to keep track of unread posts.

My question is: What would be the best way to check if a post/thread has been read by each individual user, no matter what computer they're on?

 

I can't seem to think of any *good* ideas. I've searched the forum, found a few solutions, maybe. Here is what I've got:

 

1) Add another column to the users table called "read_posts", then store all the posts that they have read in a comma separated string.

2) Make another table with the users id and the posts id, call the table "read_posts".

3) Check the users last update time and compare against the time of posts.

 

None of them seem to be an okay solution. So can anyone shed some light on a better idea?

 

Cheers!

[iversonm]

assuming each row is a topic, create a new column for each topic and add the username or userid to that last column

when loading all the topics, search the column for the username, if it is not included then it is unread. when the user views the topic update the column to include their name. when someone posts mark it as unread or new posts by deleting their names

 

 

just use explode() to make the array then search the array for there userid or username whatever makes them independent and if it doesnt come up with anything then they have not read it...

 

make sense?

[JasonLewis]

Yes, but that's still not a very good solution.

Imagine you have 10,000 members (PHPFreaks has like 70,000), the field would be HUGE.

 

There has to be something else... I've been thinking on it for a while and it's not helping, I just keep going round in circles.

[PFMaBiSmAd]

You cannot store a permanent indicator of all "read" posts. The amount of storage would exceed that needed for the actual information. Imagine what would happen if someone signed up on this forum and he marked all existing 664,831 posts as being read. You would not and could not add that many entries to a table or add their user information to that many entries.

 

You need to use a date/time "bookmark" system. You could probably examine the SMF (forum) code to see how they do it.

 

You basically have a "bookmark" column in the user data table. When someone signs up, the value is set to the current date/time. This prevents ALL the "unread" items before that point from showing up.

 

You then store unread items in a short term table. When someone visits your site (any type of activity/page request), add all the items that are newer than their last "bookmark" date/time to an "unread" table, using their user id as the key, and update their "bookmark" date/time to the newest data/time on the items that were just added to the short term table. When you display the items for that visitor, if the item is in the short term table, mark it as "new". As they read the items, remove that row from the short term table. If they click on a "mark all items as read" link, remove all their rows from the short term table. If they click on a "mark this item as unread" link, add that item to the short term table.

[DarkWater]

assuming each row is a topic, create a new column for each topic and add the username or userid to that last column

when loading all the topics, search the column for the username, if it is not included then it is unread. when the user views the topic update the column to include their name. when someone posts mark it as unread or new posts by deleting their names

 

 

just use explode() to make the array then search the array for there userid or username whatever makes them independent and if it doesnt come up with anything then they have not read it...

 

make sense?

 

You never, ever, ever, never ever never store a comma separated string in a field in order to explode() it in PHP.  That goes against database normalization.

[JasonLewis]

Mmmmm... I've tried to look at SMF's code, but it was an older version. Might get the newer version and hopefully there code is easier to follow.

 

I'll investigate your idea PFMaBiSmAd. Cheers.

[JasonLewis]

Just noticed something, when you signup on an SMF forum, all posts prior to you signing up appear as new posts. I'm confused now. I just can't get my head around this at all, and it's so frustrating.

[PFMaBiSmAd]

Code can do anything you want it to. The reason I suggested starting the bookmark datetime at the signup date was to prevent a lot of extra processing using the method I suggested. You could instead go back an amount of time of your choosing (6mo, 1yr, 2yr, very first item present...) depending on what type of information you are dealing with.

[Daniel0]

Just say that after something like 30 days then it's not considered "new" anymore. Then store read info for all people in the database and delete it after 30 days.

[JasonLewis]

I'm still going over PFMaBiSmAd suggestion. What I don't get is, you said don't store "permanent" indicator. But say someone registers and I want all posts to be considered unread. So I set the bookmark to say the time of the first post. Say there are 600,000 posts. So the user registers and ALL the posts are added to this short-term table. What if the user never comes back and views the posts? The table would have at least 600,000 entries.

Maybe I'm misinterpreting your post. But I'm still really confused.

 

Man I feel SO slow!

[Daniel0]

Uhm... if you are a new user then how can you have read the posts? I'd expect posts to be unread when you've first registered. Besides, if you go with the timeout I said above then you would only have 600k entries if there had been that many posts within the last 30 days (or whatever timeout value you choose).

[JasonLewis]

Huh? Isn't that what I said, that when you're registered all posts are unread to you?

 

You basically have a "bookmark" column in the user data table. When someone signs up, the value is set to the current date/time. This prevents ALL the "unread" items before that point from showing up.

Yep, done.

 

You then store unread items in a short term table. When someone visits your site (any type of activity/page request), add all the items that are newer than their last "bookmark" date/time to an "unread" table, using their user id as the key, and update their "bookmark" date/time to the newest data/time on the items that were just added to the short term table.

Okay, so this means that if the user registered and there were 600,000 posts already posted I would need to add all 600,000 unread posts to this table. Right?

 

Am I totally off the ball here...

[Daniel0]

Why would you want to store what people haven't read? That doesn't make sense. This means that if you have 1M members then each time a post is made you'll need to insert 1M additional rows (one for each user). You should store what people have read instead...

 

I.e. a table like this:

CREATE TABLE read_info (
  post_id bigint(20) unsigned NOT NULL,
  user_id bigint(20) unsigned NOT NULL,
  read_at datetime NOT NULL,
  PRIMARY KEY (post_id, user_id)
);

 

Then each time a person reads something you insert the relevant information into the table.

[matmunn14]

That would just create a massive table. If 10,000 members read one post then he would need to insert the same piece of data 10,000 times. Now if 10,000 members read 100 posts then that equates to 1,000,000 rows in a table. That seems like a ridiculous amount of data.

 

This could probably be fixed if he takes your other suggestion and doesn't check against posts that are greater than a timeout period, your example of 30 days.

[Daniel0]

To further decrease the size of the table you could also delete all rows associated with a specific topic id each time a new reply is added to it. Seeing as it would be unread to everybody then it would make sense to delete everybody's read info for that specific topic.

[JasonLewis]

Alright cheers! I think I've got it working. No doubt something will come up but we'll see. Thanks for that last tip too Daniel0. Will implement that.