Check if user has viewed something

Question

[edit] Removed it, because it is not worth it.

Answer 1

The exact field types will vary some depending on the database you're using, but here's the general technique:

You need a users table with unique IDs:

CREATE TABLE users (
    user_id       INTEGER      PRIMARY KEY,
    email         VARCHAR(50)  NULL,
    password      VARCHAR(32)  NULL
);

And a table for your news items:

CREATE TABLE articles (
    article_id    INTEGER      PRIMARY KEY,
    title         VARCHAR(50)  NULL,
    pubdate       DATETIMESTAMP,
    body          blob or whatever your database supports
);

And finally a table that indicates which users have read which articles:

CREATE TABLE users_articles (
    article_id    INTEGER,
    user_id       INTEGER,
    read_date     DATETIMESTAMP
);

The users_articles table should probably be indexed by article_id, depending the queries you use and how your database chooses to optimize those queries.

Now, to get all of the articles from the last 7 days that user_id 999 has not yet read, your query would look something like this:

SELECT a.title, a.pubdate, a.body
FROM articles a
WHERE a.pubdate > date_sub(NOW(), INTERVAL "7 days")
AND NOT EXISTS (
    SELECT * 
    FROM users_articles ua
    WHERE ua.article_id = a.article_id
        AND ua.user_id = 999
)

Other formulations of this query are possible, of course. And the interval syntax will vary from one database to the next. But that's the gist of it.

Whenever a user reads an article, you can insert/update the users_articles table with the user_id and article_id and the current timestamp. As a side-effect, this also gives you the information about what articles the user has read most recently.