SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    archiving read messages??

    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? .

  2. #2
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  3. #3
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  5. #5
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that is correct.

  6. #6
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ian Anderson
    www.siteguru.co.uk

  7. #7
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    Code:
    -- 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:

    Code:
    -- 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:

    Code:
    -- 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
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  8. #8
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks so much for your replies, we will give it a try and let you know if it works!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •