Inserting a NULL value into MySQL from PHP using a NULL-valued variable

2 Views

I’m using the WordPress DB interface to insert some nullable row values.

The function:
$wpdb->insert( $table, $data, $format );

takes an array of values in the $data param. If I exclude certain field values from the $data array, the fields show up null in the table. However, I want to be able to populate the $data array with values for all the fields, but make those values null when I need to. However, if I pass values that = NULL, the database values are not null, but blank. What should I do?

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Looking at the wpdb source code, the “insert” method ends up being mapped into SQL that places every field value between single-quotes (line 1233):

        $sql = "{$type} INTO `$table` (`" . implode( '`,`', $fields ) . "`) 
                VALUES ('" . implode( "','", $formatted_fields ) . "')";

    To insert a real NULL, you need to place an unadorned NULL word into that expression, and it’s not allowed for. So what is being inserted is the 4-letter string “NULL”.

    Note that this method leaves it to the user to be inserting the proper data types and depends on MySQL to automatically convert quoted e.g. numerics into real numeric values.

  2. Looks like the following works:

    Instead of:
    $nullable_value = NULL;

    do:
    $nullable_value = mysql_escape_string("NULL");

    For explanation see thread answers here and here.

    EDIT:

    On second glance, this won’t work in DB, for the reason @le dorfier states…