12

When i try something like this

    $status = NULL;

    $wpdb->update(
            'table',
            array( 
                'status' => $status,
            ), 
            array( '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?

9

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:

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.

  • 1
    support for setting NULL was added in r34737, so there is no longer any need for a workaround – dmsnell Jul 11 '16 at 22:47
3

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.