How To Set A Value To Null When Using Zend_db

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::upd

Solution 1:

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

Solution 2:

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

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

Solution 4:

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:

publicfunctionfetch()
{
    $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;
    }
}