[Pugged] Tracking Read Topics/Messages, and the "unread" flag.

[ciapug@ciapug.org]

Tim Perdue <tim@perdue.net> said:

> You could also create a separate table in the database like:
> 
> read_messages table:
> --------------------
> | user_id | msg_id |
> |   5     |   67   |
> |   5     |   68   |
> |   5     |   61   |
> |   5     |   90   |
> --------------------
> 
> As each message is read, you insert a row into that table for the user.
> On reading, you left-join your forum SELECT query against that table, pulling out
> the matching rows for the corresponding user_id.
> 
> Probably the easiest and best-performing way is how you have already done it.
> 

This is probably the most powerful way to do it. This is how the IMAP server I use does it (Cyrus IMAP). Each user has a db called "seen" that keeps track of which messages have been read. It uses Berkely DB, which I don't know how to use... so I've never looked at the layout, but I would assume it's similar to Tim's example.

I use the same kind of thing, for example if I have a User table, and a Category table, and each user can belong to multiple categories. It avoids the problem that Angie brought up where you'd have to have multiple columns in the User table for each category.

To query for this, you just do a "LEFT OUTER JOIN...", then in the WHERE clause you filter for the ID of the child table Category type. Those kinds of queries run pretty fast.

To use the Forum example, it might be something like:

SELECT * FROM forum
LEFT OUTER JOIN seen ON seen.msg_id = forum.msg_id
WHERE forum.topic = $mytopic
AND seen.is_seen = 0
ORDER BY forum.dt_entered
LIMIT 0,10

.. that would give you all the records that haven't been seen yet.
Or you could do the same thing except:

SELECT forum.*, seen.is_seen ...

..get rid of the "AND seen.is_seen = 0", then when you loop thru the records & display, you show a different status depending on the seen.is_seen value.

-dc