CODEWITHSUNDEEP
phpmysqlforums
Exoon
Exoon

Reputation: 1553

Mark forum as read when user reads it, then mark as new when new reply is posted

Ive got two tables, One for topics and one for replys.

+------------------+     +------------------+
| forum_topics     |     | forum_posts      |
+------------------+     +------------------+
| topic_id         |     | post_id          |
| topic_title      |     | post_content     |
| topic_contents   |     | post_date        |
| topic_date       |     | topic_id         |
| topic_keywords   |     |                  |
+------------------+     +------------------+

I want it so when a user views the page with the topic on and reads the topic it gets marked as read for that specific user. But if another user comes and replies to that thread, I want it to marked as new post since last visit or somthing similar.

Any ideas?

Thanks in advance.

Upvotes: 3

Views: 1568

Answers (2)

Fluffeh
Fluffeh

Reputation: 33532

You can do this with a lookup table connected to the user and forum posts like this:

When the user views a forum, enter a row into the lookup table like 

userID, topic_id, post_id, currentTime

Then to determine whether the user has already read this forum, you can query the table to see whether their userID appears in it - this means they have read it.

You can then compare the currentTime column to the last entry in the forum/thread to see which is greater which will tell you whether the user has seen the last post or not.

Edit: Yes, you will need to add a datetime column to the topics and posts (it is normally good practice anyhow to know when a post was made) and as for some PHP/SQL:

<?php
$userUpdateTime = gmdate(); // assumed to be either dateTime or unixTimestamp of when user last red the topic/forum
$qry = '
SELECT
    IF(`forum_topics`.`updateDateTime` > "'.$userUpdateTime.'", "unRead", "Read")
FROM
    `forum_topics` ';

Upvotes: 2

Matthew
Matthew

Reputation: 48304

I use the following per thread per member table on my forums:

CREATE TABLE `forum_thread_member` (
  `thread_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `member_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `last_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `read_flag` bit(1) NOT NULL,
  `posted_flag` bit(1) NOT NULL,
  `new_flag` bit(1) NOT NULL,
  `mark_flag` bit(1) NOT NULL,
  `bookmark_flag` bit(1) NOT NULL,
  `hidden_flag` bit(1) NOT NULL,
  PRIMARY KEY (`thread_id`,`member_id`),
  KEY `bookmark_flag` (`bookmark_flag`)
)

When querying the list of threads, I left join on it. If the values are NULL, then the thread hasn't been read yet. This lets you know exactly which threads a person has read and participated in.

The last_post_id is useful because you can visually jump to and display the last post the person read. The new_flag is set to 1 every time somebody posts, so that is easily used to visually determine if a list of threads has new (unread) content. The other flags are used to manage bookmarks, hiding threads, etc.

Of course this table can get quite large, but you can safely delete old records as long as none of the important flags have been set (e.g., bookmark_flag). If a person searches or browses for old threads, you can just assume they have been read (instead of assuming they are unread, like you would for new threads).

Upvotes: 0

Related Questions