Processing Web Input with MySQL - MySQL - page 715

tedious and difficult to enforce. An easier approach is to use MySQL's INSERT … ON
DUPLICATE KEY UPDATE syntax to insert a row with a count of 1 if it does not exist, and
update its counter if it does exist:
INSERT INTO hitcount ( path , hits ) VALUES ( 'some path' , LAST_INSERT_ID ( 1 ))
ON DUPLICATE KEY UPDATE hits = LAST_INSERT_ID ( hits + 1 );
SELECT LAST_INSERT_ID ();
The first time you request a count for a page, the statement inserts a row because the
page isn't listed in the table yet. The statement creates a new counter and initializes it to
one. For each request thereafter, the statement updates the existing row for the page
with the new count. No advance page registration in the hitcount table is required.
If your API provides a means for direct retrieval of the most recent sequence number,
a further efficiency is gained by eliminating the SELECT statement altogether. For ex‐
ample, in Perl, you can update the count and get the new value with only one SQL
statement like this:
$dbh -> do ( "INSERT INTO hitcount (path,hits) VALUES(?,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE hits = LAST_INSERT_ID(hits+1)" ,
undef , $page_path );
$count = $dbh -> { mysql_insertid };
To make the counter mechanism easier to use, put the code in a utility function that
takes a page path as an argument and returns the count. In Perl, a hit-counting function
might look like this, in which the arguments are a database handle and the page path:
sub get_hit_count
{
my ( $dbh , $page_path ) = @_ ;
$dbh -> do ( "INSERT INTO hitcount (path,hits) VALUES(?,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE hits = LAST_INSERT_ID(hits+1)" ,
undef , $page_path );
return $dbh -> { mysql_insertid };
}
The CGI.pm script_name() function returns the local part of the URL, so use
get_hit_count() like this:
my $count = get_hit_count ( $dbh , script_name ());
print p ( "This page has been accessed $count times." );
The technique is analogous for other languages. For example, the Ruby version of the
hit counter looks like this:
def get_hit_count ( dbh , page_path )
dbh . do ( "INSERT INTO hitcount (path,hits) VALUES(?,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE hits = LAST_INSERT_ID(hits+1)" ,
page_path )
return dbh . func ( :insert_id )
end