current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

share|improve this question

3 Answers 3

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);
share|improve this answer
    
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
up vote 3 down vote

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);
share|improve this answer
    
Totally missed the %d, good catch :) –  Clive♦ Aug 9 '12 at 15:43
up vote -1 down vote

Pretty old question, but I think this is a more universal and elegant way for both INSERTing and UPDATEing fields with optional NULL values:

If you reserve some value as the default for NULL (like empty string for strings or zero for integers), then you can use the SQL NULLIF function:

$a = null; //or false, or empty string
db_query("INSERT INTO my_table VALUES (NULLIF('%s',''))", $a);

$b = "null"; //or other keyword
db_query("UPDATE my_table SET my_field = NULLIF('%s','null')", $b);

$c = 0; //or null, or false etc
db_query("UPDATE my_table SET my_field = NULLIF(%d,0)", $c);
share|improve this answer
    
Any comments? Have missed something? –  har-wradim Sep 20 '14 at 16:27

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.