Sometimes I come across a time saver so useful that I feel compelled to share it.

Supposing we are keeping track of stats on our website in a MySQL database with a table of
columns url, month, hits.  To update the hit count we can do this:

UPDATE `stats` SET `hits` = `hits` + 1 WHERE `url` = '/tips.htm' AND `month` = '6'

Now, this would work as long as we are assured the record exists.  But, we can’t be sure it exists.

So we have to do the following:

   $results = query( "SELECT hits FROM `stats` WHERE url = '/tips.htm' AND month = '6'" ); 
   if( $results->count() == 0 ) { 
     // do insert 
     query("INSERT INTO `stats`( `url`, `month`, `hits` ) VALUES ( '/tips.htm', '6', 0 )"); 
     
   } else { 
    
     // do update 
     query("UPDATE `stats` SET `hits` = `hits` + 1 WHERE `url` = '/tips.htm' AND `month` = '6'"); 
   } 

However, this can be done in one query so long as url and month are setup as composite keys, and

hits must default to 1.  With a little setup the next query accomplishes everything in one fail swoop:

query("INSERT INTO `stats`( `url`, `month` ) VALUES ( '/tips.htm', '6' ) ON DUPLICATE KEY UPDATE `hits` = `hits` + 1"); 

Now do something productive with all the time you saved.  Keep in mind that this syntax is valid in MySQL 4.1.0 and later.

Tags: ,