Incorporate Insert On Duplicate Key Update Method in Zend_Db_Table

November 13th, 2011 by Mark Lewis

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);
	}
}