How can I update or insert a row with NULL fields using query builder?

Question

In Drupal 7, I'm being sent an array of values from a user submission that are being mapped directly to a database table.

Some of those are blank and I'd like to set those fields to SQL-style NULL in the database.

To do this, my values array has the empty or blank fields set to PHP's NULL:

array('field1' => 'value1', 'field2' => NULL)

And I'm using db_update() like this:

$result = db_update('table_name')                                                           
  ->fields($record)                                                                                   
  ->condition('cmid', $form_state['values']['cmid'])                                                  
  ->execute();

However, db_update() doesn't appear to like NULL values in the $record array. I'm not seeing any sort of errors, unfortunately, except that no updates are being made to the database and that $result is returning a falsy value.

How should I be using db_insert() or db_update() if some my values may be blank at times, and I'd like these to equate to setting those respective database fields to NULL?

Answer 1

db_update() does allow NULL to be used for field values. To confirm this, check out the core table, block_custom, which allows both block_custom.body and block_custom.format to be NULL.

First, insert some dummy data either through the block administration screen or via SQL:

INSERT INTO block_custom (body, info, format)
  VALUES ('Lorem Ipsum', 'Test block', 'plain_text');

Now run the following code:

db_update('block_custom')
  ->fields(array('body' => NULL))
  ->condition('info', 'Test block')
  ->execute();

If you look at the table after executing, block_custom.body will be set to NULL.

db_insert() works too:

$fields = array(
  'body' => NULL,
  'info' => 'Test block 2',
  'format' => 'plain_text',
);

$result = db_insert('block_custom')
  ->fields($fields)
  ->execute();

So there's something else wrong with your statement. Two things to check:

• Ensure $record really has NULL values in the db_update() query. var_dump() the array just before the db_update() and ensure nothing's modified the values.
• Check the condition statement. If set to a condition that doesn't match any of the rows in the table, the update won't occur and $result will contain a falsy value. Since it's perfectly value to have an UPDATE query that matches no rows, it won't throw an exception or return an error.

Answer 2

The following thread seems to contain your answer:

Null Values Not Inserted Into Database Using db_insert.

Specifically:

As your error message states, the column does not allow null values. Since the database won't accept null values for that table.column, you will need to send it something other than null, i.e. an empty string '' or whatever you decide the appropriate non-null value is.

There is no getting around a column level constraint on a database unless you modify the table.column so that it allows nulls. That is not recommended, since such constraints are defined for a reason

This answer also seems to give some more insight into the Field API: PDOException: Incorrect decimal value for a field which is legitimately null