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.
