How can I update or insert a row with NULL fields using query builder? 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' =&gt; 'value1', 'field2' =&gt; NULL)


And I'm using db_update() like this:

$result = db_update('table_name')                                                           
  -&gt;fields($record)                                                                                   
  -&gt;condition('cmid', $form_state['values']['cmid'])                                                  
  -&gt;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?
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')
  -&gt;fields(array('body' =&gt; NULL))
  -&gt;condition('info', 'Test block')
  -&gt;execute();


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

db_insert() works too:

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

$result = db_insert('block_custom')
  -&gt;fields($fields)
  -&gt;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.