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

Question

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.

Answer 1

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` ';