Check if user has viewed something

Question

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

I have tried nothing, because I try to grasp the concept. I haven't got much MYSQL experience. So there is not much to try. How can I try something when I do not have anything to start from? — Skid Kadda, Nov 19 '12 at 21:46
So what you are saying is that you want free code? You need to make a bit of effort on your own. StackOverflow is more for specific problems than vague requests. You should google some key words, read some tutorials and go from there. There are plenty of good SQL tutorials, plenty of good authentication tutorials and plenty of good PHP tutorials. — Sheena, Nov 19 '12 at 21:57
Nope I want guidelines. The more I will know, the better I can google and start learning what I should learn. — Skid Kadda, Nov 19 '12 at 22:08
'Visit google, plenty this that whatever'. You have a firm grasp of the obvious. Congrats. Google only works if you know where to start. btw..StackOverflow is what I make of it. — Skid Kadda, Nov 19 '12 at 22:38

slashingweapon · Accepted Answer · 2012-11-19T22:09:38.990

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.

Database design is a large discipline all on its own. There'a good post on [Database Design](http://stackoverflow.com/questions/145689/relational-database-design-patterns) you might want to look at. Also, if you found my post helpful, please upvote and/or accept it. Reputation is our only reward here. — slashingweapon, Nov 19 '12 at 22:26
I will upvote in the near future. My rep is to low.But accepted I did. — Skid Kadda, Nov 19 '12 at 22:33

score 0 · Answer 2 · answered Nov 19 '12 at 21:48

0

I suggest to make a new table where you can save the relation between the article and the user. The table will look something like this:

newsId | userId | ip | date ...

answered Nov 19 '12 at 21:48

mineichen

470
2
11

This depends on your hardware, your code, amount of current users... But in most cases this shouldn't be a problem – mineichen Nov 19 '12 at 21:58

score 0 · Answer 3 · answered Nov 19 '12 at 21:48

0

You've got a users table. And you've got a news table.

You just need something like a user_has_read table...

id
time
user_id
news_id

That way you can add an entry to this table when users first view something to tie them to the news item. Then lookup their user_id in this table to see if they've been here before.

You can now also have a "Recently Viewed" section with links to the last 10 things they've read for ease of navigation.

answered Nov 19 '12 at 21:48

HappyTimeGopher

1,377
9
14

It will only get 1 INSERT each time a user views a new article. It's not an issue. – HappyTimeGopher Nov 19 '12 at 22:37

score -1 · Answer 4 · answered Nov 19 '12 at 21:40

-1

You could have a boolean flag to determine if something was read or not. When the user gets to whatever you want him/her to read, you can go to the database and set that flag to true, showing that that record has already been seen by a specific user.

answered Nov 19 '12 at 21:40

Naner

1,292
6
25
43

A field of type bit in the existing table would do just fine, where you would have 0 for false, and 1 for true. Then if you wanted only news that were not yet read you would do something like: select * from news where read = 0 – Naner Nov 19 '12 at 21:47
You can't create the field on the news table, because its in relation with the User. If you have this flag on the news-table, its marked as readed for every user after one user has read it, and this is not the desired result – mineichen Nov 19 '12 at 21:53
Ok, I'm sorry. Yeah, you should have two tables and another one to relate them. One being News (NewsId, News, Date...), another being User (UserId, Name...), and a third one being NewsUser (NewsId, UserId, Date...). By doing this way you wouldn't need a flag to says if the news was read, you could just check if a record exists for that user and news. – Naner Nov 19 '12 at 21:53
Don't take so much care about performance. This is peanuts for current database-systems. Even complicate querys with many joins are often no problem. And if you'll really have performance-problems you could take care to optimize database access later... (i think you won't have) – mineichen Nov 19 '12 at 22:09
The query would look somewhat like this: select n.* from news n where not exists (select userid from newsuser where newsid = n.newsid and userid = 123) – Naner Nov 19 '12 at 22:14

Check if user has viewed something

4 Answers4