Keeping track of what a user has read on a web site

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Just looking for suggestion on how to do this in my Web application.

The goal is to keep track of what a user has and hasn't read and present him or  
her with new material

I am currently doing this by aggregating new content from all databases into a  
single indexed database and then saving a timestamp in the account database  
(for the current user) that tells me when the user last read items in the  
aggregated database.

This works as designed, but I don't have detailed control. If the user opens  
the page where new items are listed and reads on of the items (and don't reset  
the timestamp), that item won't be removed from the list.

This is evident when I want to have a function in my page that alerts the user  
if there is new content of a specific kind (for example: "1 new articles on  
cooking"). That function will report that until the user has reset the  
timestamp (by visiting the 'what's new?' page that lists all new articles). I  
can't mark just this article as 'read'.

So, what options do I have? Well, each item have an ID, so if I should keep  
track of read/unread I should base that on the IDs in the aggregation database.

Looking at how newsreaders (specifically those that make use of a .newsrc file)  
do it, they keep track of series of ID's, like "12,14-67,69" - which in my case  
could mean that the user has read the items with ID 13 and 68.

The aggregation database looks something like this:

ID    | Kind        | Headline                      | Original ID
    1 | article     | Home made pie                 | 23
    2 | article     | Hamburgers a'plenty           | 24
    3 | forum       | Anyone likes strawberries?    | 298
    4 | comments    | Re: Home made pie             | 67

Get the idea? The ID is the id in the aggregated database, the kind is from  
what original database the content came from and the original ID is the id in  
that database

So, if I go and read "Hamburgers a'plenty", it should perhaps update my profile  
to say "1,3-4" or somesuch to note that I have read id number 2. Or perhaps I  
should just keep track of all the IDs I have read? The aggregate database keeps  
content around for about a month, which could mean thousands of items.

I am guessing that a MySQL query that looked like this:

    "select * from aggregate where id not in(1,2,3,4,5,6,7,8.....1678)"

would be bad.

So, I am wondering how YOU would have done - Or are you already doing this in  
one way or the other? I'm just venting here and hoping that someone will come  
with good suggestions on how to solve this in an efficient manner.

One way - I suppose - would be to make on SQL query that fetches all the  
(potentially thousands) of ID's and all the headlines and puts them in an array  
and then I run that array in a PHP function that filters out items based on the  
"1,4-7,12-78" filter. I would also have to build PHP functions to maintain  
these lists, but that's a minor problem.

Anyway, any thoughts or suggestions are welcome.


Re: Keeping track of what a user has read on a web site

Quoted text here. Click to load it

This is one of the major limitations of timestamps.

Quoted text here. Click to load it

I think it is better to keep track of what the user *HAS* read.
Why?  You don't have to mess with user read lists when a new article
is added, but you DO have to mess with the unread list.

Quoted text here. Click to load it

Newsreaders using .newsrc keep track of what the user HAS read.
The newsrc isn't edited when a new article shows up.  The newsrc
approach of using ranges is a compact way to store what has been
read, but a bit awkward to manipulate.  Also, if the user interface
has a "catch-up" function (mark everything read), this collapses
down to a single range starting with the lowest possible ID (e.g.

Quoted text here. Click to load it

Another approach is to keep a SQL table containing user ID and
article ID (and forum ID, if there's more than one).  An entry in
that table means that user has read that article.

Quoted text here. Click to load it

    select aggregate.* from aggregate LEFT JOIN readlist on =  
    and readlist.userid = 'this guys user id' where is null;

gets you a list of all articles this guy hasn't read.  A problem
with this approach is that readlist grows continuously over time.

Quoted text here. Click to load it

The .newsrc approach isn't too bad:  it assumes that articles are
created in sequential order, and that getting the high id of current
articles is fairly easy.  (select max(id) from aggregate).  Many
newsreaders assume that if the article id <= the max and id >= the
min not yet expired and it's not in the newsrc list, there's a
pretty good chance that it actually exists.  If it later discovers
that the article does not exist (say, trying to fetch it or its
subject line), it marks it read.

You could try putting the list of ranges into SQL.  It saves storage.
Chances are, you'd need to wipe out and re-store the entire list
of ranges for a particular user (for a particular forum, if there's
more than one) every time.

                    Gordon L. Burditt

Re: Keeping track of what a user has read on a web site (Gordon Burditt) wrote:

Quoted text here. Click to load it

Good point.


Quoted text here. Click to load it

Yes, but lookup in that table would take time, especially as time goes by and  
new articles and new forum posts arrive. The aggregate database are there to  
check up only on what's new and contains nothing but the last months fresh  

Quoted text here. Click to load it


Exactly. I'll probably store the id access line in the user table, from where I  
load info about the current user at the beginning of each page load. LIke this:

    $q=mysql_query("select * from member where email = ''
                    and md5pass = ''");

That way, I could have a function that takes the current kind and id and  
matches it to the list, something like this:

    $user["read"] = "article=12,23-56;forum=23,56-12989"
    # $article = array of current article

    if (has_read($user["read"], $article["id"], "article")){
        # The user has read this

But the has_read() function need to be really effective, since when listing 40  
articles, it should be called with each article id to check read status.



Site Timeline