I have written myself a custom forum script using php. I decided against using phpbb and others as I wanted 100% flexibility with what I was doing.

I've hit a problem though:

How do I show a user if a post is new/unread or not.

Two solutions come into mind:

1) Cookies 2) Database

I don't want to use cookies because they can be deleted by the user and the firefox side of things means they are auto deleted. Either way, I don't want to use cookies.

The database is causing me a problem because I can't seem to get the database structure sorted in my head! The first solution I could think of was:

• When a user loads the forums up, check the last time they loaded the forums up
• Check all the posts have been made since they last viewed the forums
• Enter those values into the database in a table with fields (user_id, post_id).
• that value is then deleted from the database when they view the post

The problems I am thinking with this is it's a huge database drain. It seems SO inefficient. I'm sure there are methods with arrays in fields, but I'm not really that good with arrays.

Could anyone give me an indication of a good database design for this as well as any code that has to go with it? It's driving me crazy as I just can't think of a solution that's good and efficient with the server.

Thanks a lot in advance for your help and assistance,

James.

This is somehow a good question, I've never experienced this before so I could only suggest you an idea in which I have no guarantee about its correctness.

My idea basically is:

1. Create a new field, called is_new inside a topic table. This field contains a list of values in string form, following a certain pattern. For example: 5|6|12|110|2|45. Each value between | represents a user's ID, who has read the topic.

2. Every time a user jumps into a forum, while fetching to return the list of topics, you will check if each topic is read by that user, simple by:

   • Explode the string in is_new using explode('|', $row['is_new']);
   • Now you have an array contain the values, just need to check in_array($user['id'], $list_of_ids);

3. If false, mark the topic unread, otherwise mark it read and also update that user's ID into the is_new list.

This approach seems less 'painful' than your original approach, since it only checks further more than ordinary, and as simultaneously as you fetch the list of topics. I think it will affect a little bit more if you have heaps of users.

Note: you don't have to worry about the problem of normalization since is_new contains multiple values, you only use it to check and update, no selection required.

In addition, alternatively, you can check the topics are new or not using time comparison. For example, if a topic lasts for 4 weeks and a user doesn't read it, then it returns old even though it's unread (it makes sense doesn't it? Just like a newspaper). I think you will be able to do this, quite easy indeed.

This approach is particularly applied by quite a few forum software, since it is quick and makes more sense. Remember that the browsers initially support you with determining read/unread topics, i.e. the color of hyperlink to the topic will be changed if it's visited. Now it turns back to the Cookies solution. I wouldn't worry about users deleting their cookies. This happens rarely and the users won't die just because the read topics turn unread after they delete the cookies.

Quite an open-ended topic isn't it? Hope this helps (:

A lot of the larger forum software uses a tracking table to keep up with who has read what, something like this (heavily simplified):

CREATE TABLE topic_tracking (
    user_id INT NOT NULL,
    topic_id INT NOT NULL,
    last_visit DATETIME NOT NULL,
    PRIMARY KEY (user_id, topic_id)
)

You then use a join on this table to check if a post you're displaying is read or not. Since you'll be paging your threads, this should generate relatively few additional queries (depending on how many posts you show per page).

When a user visits the thread, update this tracking table with the timestamp of their visit. Then when displaying your thread links, check this table to see if their last_visit is earlier than the last post in the thread. This also lets you show "updated" threads, not just "new" ones.

Hmm, good question.

I will toss my two cents in on how I would handle this, it may not be the most efficient solution, but here goes:

I would use a database to solve this problem. If possible create a field in the database that attaches to a specific user. Inside this field (or column or table if you prefer) store a list of "viewed" articles by the article ID.

When rendering the page for the user, retrieve their list of "viewed" articles and a list of all the article ID's available.

Loop through your Article/forum/topic result set and see if that ID matches any of the "viewed" ID's. For every Article/forum/topic entry that does not have a corresponding "viewed" match, then to that user it would be a "new" article.

This is processor/database/network intensive and requires a look up to the database each time a page is loaded that contains article references. Although with a bit of clever query design I think you could offload this kind of operation almost entirely to the database.

Also another potential solution using the database, would be that when a user first logs into the site, you fetch a list of read articles/forums at that first load point and add it to a cookie or session, this way you only hit the database for that list once (on first load), and store it in a session/cookie/hidden field, so that in subsequent requests you would only have to go to that cookie/session/hidden field instead of performing a Db look up each and every time the user views a page with a list of articles/forums/topics. Each time the user clicks a link, capture and store it in the database, and also store that article/forum ID in your cookie/session/hidden field. Once again, perhaps not the most efficient method, but with some good old fashioned gumption I am sure you will prevail. :)

Best of Luck to you!

H

A session would be a good place to store this. I'd imagine you'd only want to store the data for the last "n" threads that a user looks at or only for threads in the last "n" days.

As someone has suggested in one of the comments it's worth looking at some of the existing forums out there and see how they implement this.

While I think it's a great learning opportunity to write your own forum software in terms of efficiency of building it yourself I think that you're re-inventing the wheel somewhat.

My approach would be to save the last post id of each topic a specific user has seen in the database:

user_id   topic_id   post_id
1         2          3

So, here you know that user 1 visited the third post in the second topic, but didn't look at the first topic at all.

To check if the user has new/unread topics you can first check for topic_id's which are not in this table for that user. To check if known topics have new messages you can compare the post_id with the last post_id of each topic. If they are not equal, there are new messages.