Have a counter table with a row for each day containing a hit count. I use the date in form YYYYMMDD as a key, and a simple int as the counter. For each hit, just find the current date and increment the bucket. Each night, add all days older than 7 days to bucket '00000000'.

So for "visits this week" you'd use:

select sum(counter) from hit_counts where hit_date > dateadd(d,-7,getdate())

and for "visits all time":

select sum(counter) from hit_counts

Note: I've used Sybase/MS SQL Sever syntax here, may vary for other DB servers....

...roboticus

...and when you add to bucket "00000000" be sure to delete the (no longer needed) rows you're adding to it!

...roboticus

## SQL table schema:
    $sql = "create table if not exists views (
        bucket integer,
        id integer null,
        views integer DEFAULT (0)
    );";
[download]

sub api_get_views {
    my $id = shift;
    my $increment = shift;

    ## get views so far
    my $views = database->prepare("SELECT * FROM views WHERE id = ?; "
+) or error database->errstr;
    $views->execute( $id );

    my ($today_bucket) = split(/\s/, HTTP::Date::time2iso());
    $today_bucket =~ s/-//g;

    my $cnt=0;
    my $bucket_exists;
    while( my $bucket = $views->fetchrow_hashref ){
        $cnt += $bucket->{views};
        $bucket_exists = 1 if $bucket->{bucket} == $today_bucket;
    }

    if($increment){
        if($bucket_exists){
            my $views = database->prepare("UPDATE views SET views = vi
+ews + 1 WHERE bucket = ? AND id = ?; ") or error database->errstr;
            $views->execute( $today_bucket, $id );
        }else{
            my $views = database->prepare("INSERT INTO views (bucket,i
+d,views) VALUES (?,?,?); ") or error database->errstr;
            $views->execute( $today_bucket, $id, 1 );
        }

        $cnt++;
    }

    return $cnt;
}
[download]