Is there a way to do an "INSERT...ON DUPLICATE KEY UPDATE" in Zend Framework 1.5?
Solution 1
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");
Solution 2
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.
Solution 3
@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';
Solution 4
$arrayData = array('column1' => value1, 'column2' => value2, ...)
class Model_Db_Abstract extends Zend_Db_Table_Abstract
{
protected $_name;
protected $_primaryKey;
public function insertOrUpdate($arrayData)
{
$query = 'INSERT INTO `'. $this->_name.'` ('.implode(',',array_keys($arrayData)).') VALUES ('.implode(',',array_fill(1, count($arrayData), '?')).') ON DUPLICATE KEY UPDATE '.implode(' = ?,',array_keys($arrayData)).' = ?';
return $this->getAdapter()->query($query,array_merge(array_values($arrayData),array_values($arrayData)));
}
}
USAGE:
eg. Model_Db_Contractors.php
class Model_Db_Contractors extends Model_Db_Abstract
{
protected $_name = 'contractors';
protected $_primaryKey = 'contractor_id';
...
}
IndexController.php
class IndexController extends Zend_Controller_Action
{
public function saveAction()
{
$contractorModel = new Model_Db_Contractors();
$aPost = $this->getRequest()->getPost();
/* some filtering, checking, etc */
$contractorModel->insertOrUpdate($aPost);
}
}
Solution 5
Use this instead:
REPLACE INTO sometable SET field ='value'.....
This will update if exists or just insert if not. This is a part of the standard mysql api.
Related videos on Youtube
Comments
-
danielrsmith almost 4 years
I would like to use
ON DUPLICATE KEY UPDATE
in Zend Framework 1.5, is this possible?Example
INSERT INTO sometable (...) VALUES (...) ON DUPLICATE KEY UPDATE ...
-
danielrsmith over 15 yearsYes that is what I had done as well, I was really wondering if there was a method of Zend_Db_... that provides that functionality so maybe instead of using $db->insert() we can use $db->insertOrUpdate() ... just an idea.
-
Tomáš Fejfar over 14 yearsYou can extend it and add this feature easily ;)
-
Bill Karwin over 14 yearsYes of course, but another goal was to avoid making special methods for proprietary SQL features for any brand of database.
-
Decent Dabbler about 14 yearsMinor nitpick: you should use quotes around array keys, otherwise PHP will try to evaluate them as constants.
-
Justin Johnson over 13 yearsI believe that depends on the adapter you are using
-
apfelbox almost 12 yearsNo, it will not. Replace removes any existing matching row and inserts a new one. This will result in undesired behaviour, if you do not insert all values. Example: you have a table with a
time_created
. If you use update without setting the value, it will stay the same from when you created the row. If you useREPLACE
, the row will be removed, a new will be inserted andtime_created
will be set to the current time. -
Patrioticcow over 9 yearsnot sure why
array_merge(array_values($arrayData),array_values($arrayData))
butarray_values($arrayData)
will suffice -
Pawel over 9 yearsGood question, I don't remember why I put it there.
-
Pawel almost 9 yearsIt is required for correct SQL query to bind second part of query (ON DUPLICATE KEY UPDATE ... = ?) with array values.
-
jmkgreen almost 9 yearsThe array keys need to be of the form ':id', not 'id' else you end up with a columns not matching error from Zend.
-
albanx almost 6 yearsGreat solution. I am using this in ZF3.
-
Valery over 5 yearsYou can do that. It could be a working solution. But it is quite inefficient. That's why people are down-voting it.