PHP Musings

I got side-tracked from working on this project for awhile, but I am trying to get back to it now.

One thing I needed to address was that for many of the database tables I would have situations where a user supplies some data, and if it already exists in the table then I just need the primary key for it, or else I need to insert it and then get the key. Typically I’ve seen people do something along the lines of a select query to see if it’s in there, and if not perform a second query to do the insert.

While thinking tonight about how ugly that is, it came to me that maybe I could do an INSERT…ON DUPLICATE KEY UPDATE query. What I wasn’t sure about was if you could get the insert ID if it already existed and therefore does an update. Upon reading the MySQL manual entry on it, it looked like it should work, but I still wasn’t positive the PHP mysql_insert_id() function would work with it, and even less sure the related CodeIgniter database functions would. After a bit of testing, the good news is, it works. This should save me doing extra queries in a number of places, plus eliminate any need for table locking (I think).

So the basic process is:

public function replace($lastName, $firstName)
{
   $lastName = $this->db->escape($lastName);
   $firstName = $this->db->escape($firstName);
   $sql = "
INSERT INTO `author` (`last_name`, `first_name`)
VALUES ($lastName, $firstName)
ON DUPLICATE KEY UPDATE `last_name` = $lastName
";
   $this->db->query($sql);
   if($this->db->affected_rows())
   {
      return $this->db->insert_id();
   }
   return false;
}

Hopefully this will save me — and maybe you — a bit of processing time and coding time.