Search code examples
mysqlzend-frameworkpdozend-db

Setting TIME field to NULL with Zend_Db

I came across what looks like an odd issue with either Zend_Db or PHP's PDO MySQL driver, that perhaps stems from my lack of knowledge of those two.

Let's assume I have a MySQL Table with a NULLable TIME field. If I run a query like this in MySQL client:

UPDATE mytable SET mytime = NULL WHERE id = 1;

Everything works as expected and mytime field will hold NULL as value after this query.

However, if I run the exact same query in PHP through the Zend_Db_Adapter, the mytime field is set to '0:0:0' after such query:

$db->getConnection()->exec('UPDATE mytable SET mytime = NULL WHERE id = 1');

How do I set that TIME field to NULL?

I'm using PHP5.3 with PDO MySQL driver, Zend Framework 1.11 and MySQL 5.1.

Solution

  • What you have should work, i.e.:

    $db->getConnection()->exec('UPDATE mytable SET mytime = NULL WHERE id = 1');

    That should work. I tested it.

    Caveat

    If the data type time is NOT NULL, then trying to set it to NULL will cause the value NULL to be defaulted to 00:00:00, which may be unexpected behaviour e.g.:

    CREATE TABLE `test` (
    `time` datetime NOT NULL
);

    Trying to insert or update the time field above with NULL will cause the value 00:00:00 to be inserted.

    This is similar for date, datetime, and a few other data types. e.g. Trying to set a data type datetime which is NOT NULL to NULL will default its value to 0000-00-00 00:00:00.

    NOTE: Mysql will not throw an error when you try to set a NULL value to a NUT NULL data type, you can change this behaviour by setting MySQL's SQL_MODE to STRICT_ALL_TABLES: see this stackoverflow question.

    The Fix

    Change the field to allow NULL and it should be fine:

    CREATE TABLE `test` (
    `time` datetime DEFAULT NULL
);

    Now the time field can be set to NULL.