How to insert a null value

Question

I want to insert a NULL value in 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 NULL :

 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 get 0 instead of NULL . What needs to be done to insert NULL ?

Answer 1

Your expression should look like

 UPDATE ... SET area_code = NULL ... 

If you type PHP null into a string just like you do, then just go to "" , i.e. to an empty line. You will need to change the value to "NULL" (string "NULL") instead of just null (type null ). You should also seriously avoid / sanitize / check your values ​​before connecting them to the SQL query, you are widely open for SQL injections.

Answer 2

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

NULL is always converted to an empty string.

when you create $string[] you cannot just concatenate your $value , or your zeros will be converted to empty strings, which will be converted back to zero. You will need to run a test, for example:

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

Answer 3

Change PHP NULL to MySQL NULL (string):

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

Answer 4

NULL is always converted to an empty string.

Try

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

since null is not a string

Answer 5

Try the following:

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

Answer 6

This is because you wrote NULL in a string:

 $field."=".$value 

You have two alternatives:

• Enter an exception for NULL values:

   if( is_null($value) ){ $string[] = $field . '=NULL'; }else{ // Please note I've also added proper string escaping $string[] = $field . "='" . mysql_real_escape_string($value) . "'"; } 

• Go to the library that supports prepared statements (IMHO, the simplest and most reliable solution):

   $string[] = $field . '=?'; $params[] = $value; 

Answer 7

First, you should avoid these published values ​​(perhaps you already have one). Secondly, NULL in PHP is always converted to an empty string ( http://php.net/manual/en/language.types.string.php ). Use instead:

 $_POST['area_code'] = 'NULL'; 

which will then correctly create the line:

 area_code=NULL 

for your UPDATE .

Answer 8

If the data type of the column of the database table is defined as int / float / numeric, then instead of NULL it takes 0. Because what you do makes it like a string that does not match the definition of the column that was int / float.