MySQL includes a clause for INSERT statements in which an UPDATE is executed if an existing primary or unique key is encountered on the update. The generate syntax for this statement is:
INSERT INTO [table] ([columns]) VALUES ([values])
ON DUPLICATE KEY UPDATE [column updates];
The Zend_Db_Table_Abstract class may be extended to incorporate a method to execute this statement. The Zend_Db classes are meant to be database agnostic, so this can only work if your database is MySQL. Since this SQL statement is a hybrid of INSERT and UPDATE, the method accepts two data arrays: one for insert and one for update. If the INSERT data encounters a duplicate key, the update array is executed on the existing row. The method will return whichever Zend_Db_Statement class you are using as the method utilizes the adapter’s query() method.
class MyProject_BaseTable extends Zend_Db_Table_Abstract
{
/**
* Execute an INSERT ON DUPLICATE KEY statement
* @param array $insertData Data to use for INSERT portion
* @param array $updateData Data to use for UPDATE portion
* @return Zend_Db_Statement
*/
public function insertOnDuplicateKeyUpdate($insertData, $updateData) {
// generate INSERT part statement
$insertCols = '';
$insertVals = '';
foreach($insertData as $column => $value) {
$insertCols .= $this->getAdapter()->quoteIdentifier($column) . ',';
$insertVals .= (get_class($value) == 'Zend_Db_Expr')
? $value->__toString()
: $this->getAdapter()->quoteInto('?', $value);
$insertVals .= ',';
}
$insertCols = rtrim($insertCols, ',');
$insertVals = rtrim($insertVals, ',');
// generate UPDATE part of statement
$updateExpr = '';
foreach($updateData as $column => $value) {
$updateExpr .= $this->getAdapter()->quoteIdentifier($column) . ' = ';
$updateExpr .= (get_class($value) == 'Zend_Db_Expr')
? $value->__toString()
: $this->getAdapter()->quoteInto('?', $value);
$updateExpr .= ',';
}
$updateExpr = rtrim($updateExpr, ',');
// generate statement
$sql = 'INSERT INTO ' . $this->_name . ' (' . $insertCols . ') VALUES (' . $insertVals . ') ';
$sql .= 'ON DUPLICATE KEY UPDATE ' . $updateExpr . ';';
return $this->_db->query($sql);
}
}
