2

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.

5

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);
  • Hey @Clive, +1 for bypassing having to deal with NULL. – RolandoMySQLDBA Aug 9 '12 at 15:29
3

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.