How to insert NULL instead of 0 for integer column?

[tamumech]

Hi all.

 

I am inserting a lot of data into a MySQL integer column via an html form.  When a field in the form is left blank, I want the value in MySQL to be NULL, but it keeps inputting '0'.  The default value for my column is NULL.  I don't really think there is a simple way to do this on the php side (using a hidden form to set blank entries equal to NULL would require an extra page), so I was wondering if MySQL had some sort of capability.  Here's a snippet.

 

UPDATE table 
SET column1 = '$_POST[$value1]', column2 = '$_POST[$value2]'
WHERE id = '$_POST[$id]'

[per1os]

As long as the column allows nulls..

 

$_POST[$value2] = isset($_POST[$value2])?"'" . $_POST[$value2] . "'":NULL;

UPDATE table 
SET column1 = NULL, column2 = '$_POST[$value2]'
WHERE id = '$_POST[$id]'

 

Should work.

[bubblegum.anarchy]

$_POST[$value1] = isset($_POST[$value1]) && is_numeric($_POST[$value1]) ? "'".$_POST[$value1]."'" : "NULL";
$_POST[$value1] = isset($_POST[$value1]) && is_numeric($_POST[$value1]) ? "'".$_POST[$value1]."'" : "NULL";

settype($_POST[$id], "integer");

query = "
      UPDATE table 
      SET column1 = {$_POST[$value1]}, 
            column2 = {$_POST[$value2]}
      WHERE id = {$_POST[$id]}";