How do I insert a NULL value

Question

I want to insert a NULL value into a table column containing the phone area code with the following properties:

mediumint unsigned DEFAULT NULL

In my PHP script, I have this check to convert an empty string to a NULL value:

if($_POST['area_code'] == "") $_POST['area_code'] = NULL;

// clean POST

foreach($_POST as $field => $value)
{
 $string[] = $field."=".$value;
}
$sql = "UPDATE Buyer SET ".implode(", ", $string)." WHERE user='$user'";

However, I am getting 0 instead of NULL value. What should I do to insert a NULL?

Answer 1

Your statement needs to look like

UPDATE ... SET area_code = NULL ...

If you're casting a PHP null to a string as you do, it'll just cast to "", i.e. an empty string. You'll need to change the value to "NULL" (the string "NULL") instead of just NULL (the type null). You should also seriously escape/sanitize/validate your values before you plug them into an SQL query, you're wide open for SQL injection.

Answer 2

From http://php.net/manual/en/language.types.string.php

NULL is always converted to an empty string.

when you build $string[], you can't just concatenate your $value, or your nulls will be converted to empty strings, which convert back to zero. You'll have to do a test like:

$value == '' ? 'NULL' : $value