archiving read messages??

[dragonfly7]

We're not sure which direction we should go. We have built a news content management area. News items are stored in a mysql database and displayed by the most current date. How would we add the capability to move these messages to an archived list once they have been read by each individual user. We don't want to physically move the items in the database as this is user specific.

Can anyone point us in the right direction or have any useful code that would help us do this as we are not sure if we should use sessions, cookies, or something else? .

[Austin_Hastings]

Seems like you really want some kind of query to differentiate between the "new news" and the "old news."

Knowing which user has read which news items is the challenge. You're looking at a hook table for that.

I'd add in another factor, like "age > 30 days", to keep events around on the page even if they have been read (just as a reminder, if you like).

So your query looks like "new news is any article less than 30 days old, or more than 30 days old if not read by 90% of users." That read-by-most bit is either a flag on the article, or a nested select with a count().

=Austin

[dragonfly7]

Not sure what a hook table is? Can you explain how that would work.

Our client doesn't want the archive to be based on the date. The messages are only to be listed in the archive if they have been read by the user.

[Austin_Hastings]

I'm not sure I understand the requirement. Is it the case that each user gets a customized "new/archive" view, where the "new" side shows articles that particular user hasn't read, and the "archive" shows articles they have read?

=Austin

[dragonfly7]

Yes, that is correct.

[siteguru]

Table: ReadNews
ArticleID | UserID

Every time a user reads an article then populate this table. Thus when you come to display articles then search this table to see if the article and user pair exists in the ReadNews table - if it does then don't display that article.

[Austin_Hastings]

Okay, then you need to record for each user if each article has been read.

Each user X each article = M x N relationship = hook table.

A hook table connects (hooks together) two other tables. It is used for multi/multi relationships:

CREATE TABLE articles (
  id INTEGER PRIMARY KEY
  -- blah, blah, blah
);

CREATE TABLE users (
  id INTEGER PRIMARY KEY
  -- blah, blah, blah
);

CREATE TABLE users_articles_read (
  user_id INTEGER NOT NULL,
  article_id INTEGER NOT NULL,
  
  PRIMARY KEY (user_id)
);

Then when the user reads an article, you insert the (user_id, article_id) pair into the hook table.

When a user logs in, you do a little set arithmetic to decide which articles go in the "new news" and "old news (archive)" categories. For new-news, you look for articles that don't have an "already-read-it" hook for this user:

-- News
SELECT
  article.id
FROM
  articles AS article
  LEFT JOIN users_articles_read AS read
    ON (article.id = read.article_id)
WHERE
  read.user_id = ?
  AND read.article_id IS NULL

For archive articles, you look for articles that DO have a matching "read-already" hook:

-- Archive
SELECT
  article.id
FROM
  articles AS article
  JOIN users_articles_read AS read
    ON (article.id = read.article_id)
WHERE
  read.user_id = ?

(Note the "LEFT" join is gone.)

And in the interest of completeness, you count them thus:

-- Counts
SELECT
  IF (read.article_id IS NULL, "unread", "read") AS status,
  COUNT(*) as count
FROM
  articles AS article
  LEFT JOIN users_articles_read AS read
    ON (article.id = read.article_id)
WHERE
  read.user_id = ?
GROUP BY status

[dragonfly7]

thanks so much for your replies, we will give it a try and let you know if it works!