MySQL and PHP - insert NULL, not empty string

Question

I have a MySQL statement that inserts some variables into a database. I recently added 2 fields that are optional ($ intLat, $ intLng). Right now, if these values ​​are not entered, I am passing an empty string as the value. How to pass an explicit NULL value to MySQL (if empty)?

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng) VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', '$intLat', '$intLng')"; mysql_query($query); 

Answer 1

To pass NULL to MySQL, you do this.

 INSERT INTO table (field,field2) VALUES (NULL,3) 

So, in your code, check if $intLat, $intLng empty , if any, use NULL instead of '$intLat' or '$intLng' .

 $intLat = !empty($intLat) ? "'$intLat'" : "NULL"; $intLng = !empty($intLng) ? "'$intLng'" : "NULL"; $query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng) VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', $intLat, $intLng)"; 

Answer 2

If you do not pass values, you will get zero default values.

But you can just pass the word NULL without quotes.

Answer 3

All you have to do is: $variable =NULL; // and pass it in the insert request. This will store the value as NULL in mysql db

Answer 4

This works fine for me:

 INSERT INTO table VALUES ('', NULLIF('$date'='')) 

(first "increment id field")

Answer 5

Your request may look like this:

 $query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng) VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$lng', '" . ($lat == '')?NULL:$lat . "', '" . ($long == '')?NULL:$long . "')"; mysql_query($query); 

Answer 6

Usually you add regular values ​​to mySQL, starting with PHP:

 function addValues($val1, $val2) { db_open(); // just some code ot open the DB $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES ('$val1', '$val2')"; $result = mysql_query($query); db_close(); // just some code to close the DB } 

When your values ​​are empty / null ($ val1 == "" or $ val1 == NULL), and you want NULL to be added to SQL, and not 0 or an empty string, to the following:

 function addValues($val1, $val2) { db_open(); // just some code ot open the DB $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (". (($val1=='')?"NULL":("'".$val1."'")) . ", ". (($val2=='')?"NULL":("'".$val2."'")) . ")"; $result = mysql_query($query); db_close(); // just some code to close the DB } 

Note that null must be added as "NULL", and not as "NULL". Non-empty values ​​must be added as ". $ Val1." '" Etc.

Hope this helps, I just had to use this for some hardware data loggers, some of which collect temperature and radiation, while others only collect radiation. For those who do not have a temperature sensor, I needed NULL, not 0, for obvious reasons (0 is also the accepted temperature value).

Answer 7

Check the variables before building the query, if they are empty, change them to NULL

Answer 8

you can do this for example using

 UPDATE `table` SET `date`='', `newdate`=NULL WHERE id='$id' 

Answer 9

For some reason, radhoo's solution will not work for me. When I used the following expression:

 $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (". (($val1=='')?"NULL":("'".$val1."'")) . ", ". (($val2=='')?"NULL":("'".$val2."'")) . ")"; 

'null' (with quotes) was inserted instead of zero without quotes, making it a string instead of an integer. So I finally tried:

 $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (". (($val1=='')? :("'".$val1."'")) . ", ". (($val2=='')? :("'".$val2."'")) . ")"; 

An empty result led to the correct null (unspecified) that was inserted into the request.