Insert NULL values into MySql

[Stingus]

Hi all,
I'm having this problem when inserting data from a POST var into MySql. Some columns in db have default NULL values. The insert query looks like this:

$Query = 'INSERT INTO table VALUES ("' . $_POST['field1'] . '","' . $_POST['field2'] . '")';

If $_POST['field1'] is empty (taken from a <input type="text" ...>) the value in the db isn't NULL, thus messing with my further code (isset checks). I want MySql to assign the default value NULL if the text field is empty.

Thanks for ideas

[Wynder]

Before you use them in the query, examine the contents of your POST data and modify as needed...

if(trim($_POST['field1']) == "")
{
   $_POST['field1'] = "NULL";
}

Depending on how you have everything laid out, you can probably cycle through all of them with a foreach and assign back to the POST or new variables.

[LordShryku]

I'd suggest building the query with the checks.

$query  = 'INSERT INTO table VALUES (';
$query .= empty($_POST['field1']) ? 'NULL, ' : "'".$_POST['field1']."', ";
$query .= empty($_POST['field2']) ? 'NULL'   : "'".$_POST['field2']."'";
$query .= ')';

I don't know how it'll react using double quotes like you are, but using the ANSI standard single quotes, it would literally write the word "NULL" to the field using Wynder's check.

[Stingus]

I thought of checking the POST vars before putting them into the query. I was hoping for a simplier solution 🙂, like a php.ini setting, but it's ok, I'll stick with a foreach check.
Thanks all for the replies.