Is there a way to do an "INSERT...ON DUPLICATE KEY UPDATE" in Zend Framework 1.5?
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.
Edit: You can avoid repeating the parameters by using VALUES()
expressions.
$sql = "INSERT INTO sometable (id, col2, col3) VALUES (:id, :col2, :col3) ON DUPLICATE KEY UPDATE col2 = VALUES(col2), col3 = VALUES(col3)";$values = array("id"=>1, "col2"=>327, "col3"=>"active");
As a sidebar, 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)';
See http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html for more information.
@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';