why "null" function doesnt work? blank form field inserts a "0" instead of null

[mac007]

Hello, all again:

 

I have this problem, where I have a form-field that even if I leave it blank it still automatically inserts a "0" (the db-field itself is numeric). I thought this should have done it, but nothing! What I want is for the db-field to still remain as NULL, not "0".

 

if (is_null($_POST['Jan'])) { $Jan = 'NULL';} else { $Jan = $_POST['Jan'];}

 

in my UPDATE statement I simply have it as:  Jan='$Jan'

 

Appreciate any help...

[allworknoplay]

How is your DB structured? What is the column type info?

[mac007]

Thanks allwork...

 

the column specs are...

 

Type: float

Length:11

Decimals: 2

Null: is checked to allow

[PFMaBiSmAd]

Jan='$Jan'

 

Quotes around a value that is assigned to a numeric field cause that value to be converted to a floating point number. Since the string 'NULL' contains no numeric value, it is converted to a zero and that is used in the UPDATE.

 

I would remove the single-quotes around the value so it is directly treated as numeric and a NULL in it means NULL -

 

Jan=$Jan

[thebadbad]

Kinda beaten to it, but here's my 2 cents

 

The trick is to insert NULL without quotes, else it's treated as a string (that's my experience at least). So you'll need to build the query depending on whether the var is null or not. I've done it like this in the past:

 

<?php
function nullCheck($var) {
return is_null($var) ? 'null' : "'" . mysql_real_escape_string($var) . "'";
}
$query = 'INSERT INTO `table` (`field`) VALUES (' . nullCheck($var) . ')';
mysql_query($query) or die('Could not insert data into database: ' . mysql_error());
?>

 

Also make sure that the field is not set to NOT NULL.

 

Edit: But since the field is numeric, you should simply just remove the quotes altogether, like PFMaBiSmAd points out. My bad.

[mac007]

You are awesome PFM!!  such a simple fix... and true, TheBadbad, all I needed to do was remove single quotes and that did it... 

 

I need to remember that when UPDATING integers or numbers I dont need to enclose $variables within quotes.. 

 

thanks!