MySQL and PHP - insert NULL rather than empty string
PhpMysqlNullSql InsertPhp Problem Overview
I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do I 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);
Php Solutions
Solution 1 - Php
To pass a NULL to MySQL, you do just that.
INSERT INTO table (field,field2) VALUES (NULL,3)
So, in your code, check if $intLat, $intLng
are empty
, if they are, 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)";
Solution 2 - Php
This works just fine for me:
INSERT INTO table VALUES ('', NULLIF('$date',''))
(first ''
increments id field)
Solution 3 - Php
If you don't pass values, you'll get nulls for defaults.
But you can just pass the word NULL without quotes.
Solution 4 - Php
All you have to do is: $variable =NULL;
// and pass it in the insert query. This will store the value as NULL in mysql db
Solution 5 - Php
Normally, you add regular values to mySQL, from PHP like this:
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 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'" . The non-null values must be added as "'".$val1."'", etc.
Hope this helps, I just had to use this for some hardware data loggers, some of them collecting temperature and radiation, others only radiation. For those without the temperature sensor I needed NULL and not 0, for obvious reasons ( 0 is an accepted temperature value also).
Solution 6 - Php
your query can go as follows:
$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);
Solution 7 - Php
Check the variables before building the query, if they are empty, change them to the string NULL
Solution 8 - Php
you can do it for example with
UPDATE `table` SET `date`='', `newdate`=NULL WHERE id='$id'
Solution 9 - Php
>For some reason, radhoo's solution wouldn't 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 null 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."'")) .
")";
>The blank resulted in the correct null (unquoted) being inserted into the query.
Solution 10 - Php
2022 | PHP 7.3 | MySQL 5.7
Accepted answer by Rocket Hazmat gives me "NULL" as a string. So I change it to:
$intLat = !empty($intLat) ? "'$intLat'" : NULL;
$intLng = !empty($intLng) ? "'$intLng'" : NULL;