Page Counter Problem

I want to have a counter on everytime a game is played on my site and im looking for the best way to do so. Im having no trouble with a total counter but i want to have a counter that shows how many times played within the last week and the last day. I was going to INSERT a query everytime it was played and then afterwords just do a mysql_num_rows on the plays table. But i would like a better solution if possible because over time the table will have many queries and slow. Any help will be appreciated.

Use an UPDATE query instead, one table containing a single row and the sql something like


"update mytable set cnt = cnt+1";

mytable
======
cnt | INT 

yeah that would work for a total counter but not for a day or the week.

Just add a datetime column and be done with it.


hit
=====
game_id | datetime

Yes but then i would have to insert a query everytime its played and then do a count which is what i figured out myself but im looking for a alternative way. I wanted a different way to save time in the future when there will end up being many queries i figured it would slow down the reponse time to do a mysql_num_rows on the table.

But you need to save the datatime too, and you don’t need to return all the results, just a count of them.


SELECT COUNT(*) AS hits FROM hit WHERE game_id = x;

You could always create a job to clear to table after a certain period too.

Okay that sounds a little better actually so this will work?
Day:

mysql_query("SELECT COUNT(*) AS hits FROM hit WHERE game_id = 'x' AND date >= '".(time() - (3600*24)."')");

Week:

mysql_query("SELECT COUNT(*) AS hits FROM hit WHERE game_id = 'x' AND date >= '".(time() - ((3600*24) * 7))."')");

Kinda.

You would be better off with:

SELECT COUNT(*) AS hits FROM hit WHERE visited > CURRENT_DATE - INTERVAL 1 DAY;
SELECT COUNT(*) AS hits FROM hit WHERE visited > CURRENT_DATE - INTERVAL 7 DAY;

:slight_smile:



$day = date('D',strtotime( 'today') );

"update mytable set $day = $day+1";

mytable
======
Mon | INT
Tue | INT
etc

At end of week tell cron to backup that table, empty it and start again.

Depends on if the OP wants data to reset on a given day, or just to preserve 7 days’ worth of data.

How about just using the date but not the time?

2011-06-08 | INT
2011-06-07 | INT
etc.

Also, doesn’t this need an INSERT … ON DUPLICATE KEY query?

INSERT INTO table (day, count) VALUES ('$day', 1) ON DUPLICATE KEY UPDATE count=count+1

@martbean - yes, a single table with day, count, something similar floated into my brain when walking the dogs, ON DUPLICATE KEY though, I have never had occasion to use that before - thanks for sharing that.