How to Set a Value to NULL when using Zend_Db

Question

When performing UPDATE and INSERT queries using Zend_Db, I frequently need to set values equal to NULL (not ''). However, the default behavior of Zend_Db::insert() and Zend_Db::update() seems to be that values that are empty are translated into empty strings ('') and put into the database as such.

Does anyone know of way to actually force a NULL value to go into fields if the value is empty in php?

Answer 1

Try setting the affected fields to: new Zend_Db_Expr('NULL')

Answer 2

While using Zend 2.4.7 for those who may be visiting this issue, I was able to use this with William Lannen's answer providing some inspiration. Assuming getTable() returns a Zend\Db\TableGateway object:

public function fetch()
{
    $data['column_name1 = ?'] = 'column_value';
    $data[] = new \Zend\Db\Sql\Predicate\IsNull('column_name2');
    $resultSet = $this->getTable()->select($data);
    if (0 === count($resultSet) {
        return 'SomeExpectationOrException';
    } else {
        return $resultSet;
    }
}

Answer 3

As Johrn I was able to do this with PHP's null value:

$value = null;
$what = array($columnName => $value);
$where = $this->_dbTableName->getAdapter()->quoteInto('Id = ?', $dbRecord->Id);
$this->_dbTableName->update($what, $where);

but encountered situations where the database itself had the column set to NOT NULL and in such cases the value was indeed converted to either empty string or in case of FLOATs to 0.00. I guess INT column would end up as 0 ;-)

Answer 4

I've always just been able to do this using PHP's null:

$toUpdate = array('nullValue' => null, 'otherValue' => 'something');
Zend_Db::update($table, $toUpdate, $where);