Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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