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?
Add a comment |
2 Answers
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.
Comments
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...
3 Comments
afarazit
$nullable_value = mysql_escape_string("NULL"); is not NULL but the string NULL, you can just leave out the NULL field in your sql empty using '' or just the word NULL without quotesafarazit
$data = array('id' => NULL, 'name' = $username); or `$data = array('id' => '', 'name' = $username);
Yarin
@atno- That's what I had originally tried, and it doesn't work- The MySQL fields will be blank, not null. See the link in my answer for more.