$wpdb won't insert NULL into table column

Question

When I try something like this:

$wpdb->update(
    'table',
    ['status' => NULL],
    ['id' => 1] 
);

In the status column now I have an empty string '', it simply won't set it to NULL.

The column can be NULL of course. I've also tested $wpdb->query and $wpdb->prepare and the results are the same. Am I doing something wrong?

Answer 1

Update:

Since WordPress 4.4. this is now supported by the insert, update, replace and delete methods of wpdb and the ticket #15158 has been closed as fixed.

Thanks to @dmsnell for commenting about that update.

On the other hand, the null support in wpdb::prepare() is currently closed as wontfix in ticket #12819.

Previous answer:

NULL not supported:

It looks like you will have to write your own custom SQL to update the value with NULL.

Currently NULL is not supported by $wpdb->prepare(), that takes the input through the vsprintf formatting function.

Check out these open Trac tickets:

• wpdb::prepare support for null

• wpdb insert & update with null values

These tickets are about 4 years old, so I wouldn't hold my breath until this gets supported by the core ;-)

You should take a look at the source as @s_ha_dum suggested.

A possible workaround:

If you're adventurous you can try the following with the query filter:

    // Add a filter to replace the 'NULL' string with NULL
    add_filter( 'query', 'wpse_143405_query' );

    global $wpdb;
    $wpdb->update(
        'table',
        array( 
            'status' => 'NULL',
        ), 
        array( 'id' => 1 ) 
    );

    // Remove the filter again:
    remove_filter( 'query', 'wpse_143405_query' );

where

/**
 * Replace the 'NULL' string with NULL
 * 
 * @param  string $query
 * @return string $query
 */

function wpse_143405_query( $query )
{
    return str_ireplace( "'NULL'", "NULL", $query ); 
}

You might want to use a more unique string than 'NULL' to replace, perhaps '###NULL###' instead.

Answer 2

wpdb->update defaults to a string for all data types.

format
(array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

You can specify a format but the allowable specifiers are:

Possible format values: %s as string; %d as integer (whole number) and %f as float. (See below for more information.) If omitted, all values in $where will be treated as strings.

http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

You can read through the source and work out the process.

If you hack the wpdb->prepare method (on a dev server that gets wiped clean periodically :) ) to dump the SQL before just before the return, you will see that the replacement happens before wpdb->prepare:

string(48) "UPDATE `table` SET `status` = %s WHERE `id` = %s"

Though, as suggested by @birgire, it may well be a limit to prepare that prompted that replacement.

Answer 3

I'd like to further explain how to do this in WP 4.4 and beyond. You need to set both the data and format element that you wish to be null to a PHP 'null' value.

The example in ticket #15158 is as follows:

$wpdb->update($ttable, 
              [
                'user_id' => NULL,
                'status' => 'available',
                'update_time' => $now->format('Y-m-d H:i:s')
              ], [
                'therapist_id' => $therapist_id,
                'user_id' => $user_id,
                'start_time' => $ub['start_time']
              ], [
                 NULL,
                 '%s',
                 '%s'
              ], [
                 '%d',
                 '%d',
                 '%s'
            ]);