By hazamonzo on

If i have a field in my table set to float (default value for an empty value is 0.00) and i want to insert a NULL value instead of the default value when an empty value is inserted.

When i leave one of my textboxes empty the sql tries to insert an empty string into that field but by default it will set to 0.00, i want to be able to check if the textbox is empty and if so, replace the node variable with the string value "NULL"

this is what i have but alas it does not work

function item_validate($node) {
if (empty($node->item1)) {
$node->item1 = "NULL";
}
}

What might i be doing wrong?

Thanks in advance, Harris

⋅ Categories: Drupal 5.x

Comments

Spacecat’s picture

Spacecat commented

NULL is used without quotes.

marcor’s picture

marcor commented

Same problem, but the solution doesn't work, neither with quotes nor without.

 db_query("UPDATE {mynode} SET
    price=%d
    WHERE nid=%d",
    NULL,  $node->nid
  );

phpMyAdmin shows:
value: 0.00
variable price is defined as: decimal(12,2) unsigned default NULL

marcor’s picture

marcor commented

db_query() calls a function that forces explicit types for %d or %f placeholders. So this code works:

$query  = "UPDATE {mynode} SET price=";
$query .= empty($node->price) ? "NULL": $node->price;
$query .= " WHERE nid=%d";
db_query($query, $node->nid);

NULL is quoted because it has to be a string in php, so it will be unquoted in MySQL.

Any more elegant solution is welcome!

criznach’s picture

criznach commented

I sometimes do the same for date fields. Just don't forget to escape your input with db_escape_string, since db_query won't be doing it for you. Failure to do so could result in a security hole. If you find yourself doing it a lot, you could write a helper function to make it a little easier and less prone to typos.

Chris.

http://www.trailheadinteractive.com
--- Featured Projects ---
http://www.montanakitesports.com
http://www.cmrussell.org
http://www.tdandh.com
http://www.cccsmt.org
http://www.universalsemensales.com

http://www.trailheadinteractive.com