Best way to store "views" of an topic

Question

I use this code to update views of an topic.

UPDATE topics 
SET views = views + 1 
WHERE id = $id

Problem is that users likes spam to F5 to get ridiculous amounts of views.

How should I do to get unique hits? Make a new table where I store the IP? Don't want to store it in cookies. It's too easy to clear your cookies.

Answer 1

I would create a separate table for storing this information. You can then capture a larger amount of data and not require updating the table that is likely to be read the most.

You would always use INSERT INTO tblTopicViews...

And you would want to capture as much information as you can, IP address, date and time of the hit, perhaps some information on browser version, operating system etc - whatever you can get your hands on. That way, you can fine-tune how you filter out refresh requests over time.

It's worth bearing in mind that many users can share an IP - for example, an entire office might go via the same router.

Answer 2

Well, you could write the individual page hits to a log table, including identifying information like cookings or IP address. You can analyze that table at leisure.

But the web server probably has a facility for this. I know both IIS and Apache can create detailed usage logs. And for both, there's a variety of graphing and analysis tools that keeps things like IP addresses into account.

So instead of rolling your own logging, you could use the web server one.

Answer 3

I would create a table which stores unique views:

CREATE TABLE unique_views(
    page_id number,
    user_agent varchar2(500),
    ip_address varchar2(16),
    access_time date,
    PRIMARY KEY (page_id, user_agent, ip_address, access_time)
)

Now if someone accesses the page and you want to allow one view per user per day, you could do

INSERT INTO unique_views (:page_id, :user_agent, :ip_address, trunc(SYSDATE, 'day'))

which won't allow duplicate views for the same user during one day. You could then count the views for each page with a simple GROUP BY (example for today's views):

SELECT page_id, count(*) page_views
FROM unique_views
WHERE access_time = trunc(SYSDATE, 'day')
GROUP BY page_id

Answer 4

You could use the session_id() to discriminate between different users, obiously you need a separate table to track each visit.

UPDATE: I just noticed you don't want to depend on cookies, so this may not be suitable for you.

Answer 5

Note that due to various problems (eg. unknown behavior of cache servers) this kind of thing is always going to be inaccurate and a balance between various factors. However, for a rough vaguely-secure counter, using a separate table as Karl Bartel and others suggest is a decent solution.

However, depending on how seriously you take this problem, you may want to leave out "user_agent" - it's far to easy to fake, so if I really wanted to inflate my hit counter I could rack up the hits with a script that called my page with user-agent="bot1", then again from the same IP with "bot2", etc.

But then, 2 users behind one IP will be only counted as 1 hit so you lose accuracy - see what I mean about a balance between various factors?