Is there a way to do an "INSERT...ON DUPLICATE KEY UDPATE" in Zend Framework?

Question

I would like to use "ON DUPLICATE KEY UPDATE" in Zend Framework, is this possible?

Example

INSERT INTO sometable (...) VALUES (...) ON DUPLICATE KEY UPDATE ...

Answer 1

If your doing something like this, then most likely your design is flawed.

Answer 2

I was searching for this also long time, but finally I found answer only to write own sql query. Something like: $db->query('INSERT INTO table ( column ... ... ) VALUES( "'.$value.'" ... ... )

  ON DUPLICATE KEY UPDATE 
   column = "'.$value.'"
  ');

I don't found another solution

Answer 3

I worked for Zend and specifically worked on Zend_Db quite a bit.

No, there is no API support for the ON DUPLICATE KEY UPDATE syntax. For this case, you must simply use query() and form the complete SQL statement yourself.

I do not recommend interpolating values into the SQL as harvejs shows. Use query parameters. Note that you have to repeat the list of values:

$sql = "INSERT INTO sometable (id, col2, col3) VALUES (?, ?, ?)
  ON DUPLICATE KEY UPDATE id = ?, col2 = ?, col3 = ?";

$values = array("id"=>1, "col2"=>327, "col3"=>"active");

$db->query($sql, array_merge(array_values($values), array_values($values)));

I included id = ? even though it's obviously a no-op when the key is a duplicate, but it's handy to include it because it's easier to clone the array of values when I don't have to remove the first entry.

Answer 4

@Bill Karwin: great solutions! But it would be greater if to use named placeholders (":id", ":col1", …) instead of questions signs. Than you wouldn’n need to duplicate values by array_marge. Also if to use "SET" syntax of "INSERT" instead of "VALUES", the code gets simplier to be generated automatically for any set of fields.

$sql = 'INSERT INTO sometable SET id = :id, col2 = :col2, col3 = :col3
    ON DUPLICATE KEY UPDATE id = :id, col2 = :col2, col3 = :col3';

Answer 5

As a sidebar to the accepted answers on this thread, you can simplify the ON DUPLICATE KEY UPDATE clause and reduce the amount of processing your script needs to do by using VALUES():

$sql = 'INSERT INTO ... ON DUPLICATE KEY UPDATE id = VALUES(id), col2 = VALUES(col2), col3 = VALUES(col3)';

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Answer 6

you can simply do something like this:

set unique index on your id

and then

try {
   do insert here
} catch (Exception $e) {
   do update here
}