Inserting NULL value with db_query OR db_write_record

Question

I have a table called foo_bar whose fields are as follows

id int(10) unsigned NOT NULL AUTO_INCREMENT
name varchar(255) DEFAULT NULL
amount float DEFAULT NULL

I want to enter the value to this table like this

$name_value = 'Dollar';
$amount = NULL;

db_query("INSERT INTO {foo_bar} (name, amount) VALUES ('%s', %d)", $name_value, $amount_value);

But now when I see the values into the table it showed me

id | name   | amount |
1  | Dollar |      0 |

I tried db_write_record as well like this...

$obj_foo_bar = new stdClass();
$obj_foo_bar->name = 'Dollar' 
$obj_foo_bar->amount = ''

But I am getting the same result.

EDIT: 17 Sep 2012

Don't use drupal_write_record for insert/update queries where you want to place the default value to NULL

INSTEAD: Use content_write_record which is used by CCK module to update the database tables were you want to place default value as NULL.

This function says Directly copied from core's drupal_write_record, which can't update a column to NULL. See http://drupal.org/node/227677 and http://drupal.org/node/226264 for more details about that problem.

Clive · Accepted Answer · 2012-08-09 14:41:48Z

up vote 4 down vote accepted

The NULL that you're passing is possibly/probably being interpreted as a string, ie. "NULL". When MySQL casts that to a float the result is 0.

If you have a default value for the column you can just leave it out of the query altogether, and you'll get the results you expect:

db_query("INSERT INTO {foo_bar} (name) VALUES ('%s')", $name_value);

or

$obj_foo_bar = new stdClass();
$obj_foo_bar->name = 'Dollar'
// Leave out $obj_foo_bar->amount

drupal_write_record($table, $obj_foo_bar);

edited Aug 9 '12 at 14:41

answered Aug 9 '12 at 14:33

Clive♦
64.5k654107

Hey @Clive, +1 for bypassing having to deal with NULL. – RolandoMySQLDBA Aug 9 '12 at 15:29

Thanks Clive - You save my day! – Sukhjinder Singh Aug 9 '12 at 15:50

RolandoMySQLDBA · Answer 2 · 2012-08-09 16:54:23Z

DISCLAIMER : Not a Drupal Expert, Just a MySQL DBA

In your code, you tried to enter NULL into a numeric field

db_query("INSERT INTO {foo_bar} (name) VALUES ('%s')", $name_value);

However, to forcibly enter a NULL you must enter it like this

db_query("INSERT INTO {foo_bar} (name,amount) VALUES ('%s',%s)", $name_value);

NOT

db_query("INSERT INTO {foo_bar} (name,amount) VALUES ('%s',%d)", $name_value);

In other words, do not use %d in db_query.

The proper setup for using NULL would be

$name_value = "Dollar";
$amount_value = "NULL";
db_query("INSERT INTO {foo_bar} (name,amount) VALUES ('%s',%s)",$name_value,$amount_value);

2 Answers