Zend_Db_Expr and how to use it

One thing that comes up a lot when updating a database is incrementing values. You might have a value stored in a column and you want to increment it by one or more but you don’t want to query the record first. This adds additional overhead to the server by running an extra unnecessary query. To get around this you can increment the column. This is pretty standard stuff using straight SQL as you can update the column using a statement like:

UPDATE myTable SET myColumn=myColumn+1 WHERE rowId='123';

While trying to fix a bug using this method and Zend_Db I realised that these kind of expressions have to be contained within a Zend_Db_Expr class. Hopefully the code example below will save someone a bit of  head scratching when trying to figure out why their columns aren’t incrementing.

This is the wrong way to do it (and will result in a failed update):

$data = array('myColumn' => 'myColumn+1');
$where = 'rowId=123';
$db->update('myTable', $data, $where);

The correct way:

$data = array('myColumn' => new Zend_Db_Expr('myColumn+1'));
$where = 'rowId=123';
$db->update('myTable', $data, $where);

Another use for Zend_Db_Expr is when you need to use any mathematical functions in your Zend_Db select statement. You can use it in your column definitions like in the example below which multiplies two columns together to report a total:

$totalGuests = 'quantity * guestsPerTicket';
$columns = array('totalGuests' => new Zend_Db_Expr($totalGuests));