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.

Answer 1

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

Answer 2

DISCLAIMER : Not a Drupal Expert, Just a MySQL DBA

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

Clive already gave you this

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

Answer 3

A lot of the answers here are recommending you use db_query for your INSERT queries.

Note that the docs state;

Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.

The code is far more elegant also;

$name_value = 'Dollar';
$amount = NULL;
$fields = array(
  'name' => $name_value,
  'amount' => $amount,
);

$record = db_insert('foo_bar')
          ->fields($fields)
          ->execute();

// $record now contains the new row from the db.

I know this is an old question but it was worth posting in case somebody else stumbles upon it.