Sometimes I come across a time saver so useful that I feel compelled to share it. This little tip will help you when you need to increment a value in mysql but do not know whether or not the record exists.

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:

   1: <?php

   2:  

   3: query( "UPDATE `stats` SET `hits` = `hits` + 1 WHERE `url` = ‘/tips.htm’ AND `month` = 12");

   4:  

   5: ?>

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:

   1: <?php

   2:  

   3: $results = query( "SELECT hits FROM `stats` WHERE url = ‘/tips.htm’ AND month = MONTH" );

   4: if( $results->count() == 0 ) {

   5:     // do insert

   6:     query("INSERT INTO `stats`( `url`, `month`, `hits` ) VALUES ( ‘/tips.htm’, MONTH, 0 )");

   7: } else {

   8:     // do update

   9:     query("UPDATE `stats` SET `hits` = `hits` + 1 WHERE `url` = ‘/tips.htm’ AND `month` = MONTH");

  10: }

  11:  

  12: ?>

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:

   1: <?php

   2:  

   3: query("INSERT INTO `stats`( `url`, `month` ) VALUES ( ‘/tips.htm’, MONTH ) ON DUPLICATE KEY UPDATE `hits` = `hits` + 1");

   4:  

   5: ?>

Now go watch some youtube with all the time you saved. Keep in mind that this syntax is valid in MySQL 4.1.0 and later