trying to INSERT NULL if input field left blank

Question

I'm trying to insert NULL into the mySQL databse with PHP.

I've got:

$myVariable=$_POST[inputfield];        
if(!is_numeric($myVariable) || $myVariable==0){

$myVariable=NULL;
} 

My database field is decimal, and IS NULL has been set. It keeps reverting to 0.00 if input is left blank. How do I set NULL if left blank, so I can call a php if NULL function?

Edit Calling if NULL in decimal is not correct?

<? if ($debit==NULL || $debit=="0.00") {echo 'Awaiting Cost';} 
elseif ($debit != NULL || $debit!="0.00") { echo "£$debit";}?>

This displays my message correctly, so if if NULL is useless for a decimal i'll just leave 0.00. Is this hacky?

SQL structure for those who asked:

 `myTableField` decimal(10,2) default NULL,

Answer 1

Try it on the SQL side:

$sql = "INSERT INTO `table` (`field1`) VALUES (IF($myVariable == 0, NULL, $myVariable))";

Answer 2

try:

$myVariable="NULL";

.
if your code is:

$val=NULL;
mysql_query("SET @v=$val");

the MySQL got the string:

SET @v=0;

it's like using :

echo "SET @v=$val";

Answer 3

If you are composing an SQL statement by appending the value of the $myVariable variable, then you should look if it's NULL or not and modify the SQL statement accordingly.

For example, if your code is something like:

$sql .= "myVariable = '" . mysql_real_escape_string($myVariable) . "'";

then you should change it to something like:

if (is_null($myVariable)) {
    $sql .= "myVariable = NULL";
} else {
    $sql .= "myVariable = '" . mysql_real_escape_string($myVariable) . "'";
}

Answer 4

you can filter field's data in MySQL itself, using a TRIGGER:

CREATE TRIGGER table1_update BEFORE UPDATE ON table1 FOR EACH ROW SET
    NEW.num=IF(NEW.num=0 ,NULL,NEW.num),
    NEW.txt=IF(NEW.txt="",NULL,NEW.txt);
CREATE TRIGGER table1_create BEFORE INSERT ON table1 FOR EACH ROW SET
    NEW.num=IF(NEW.num=0 ,NULL,NEW.num),
    NEW.txt=IF(NEW.txt="",NULL,NEW.txt);