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);
If your variable already contains null, prepared statements will do it for you without any extra code
$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
VALUES (?,?,?,?,?,?)";
$data = [$notes, $id, $imageUploaded, $lat, $long, $intLat, $intLng];
$conn->prepare($query)->execute($data);
as simple as that.
Any variable that contains a PHP null will be written as MySQL null. Not to mention this query is safe from SQL injection.
If case you want to set a variable to null if it contains an empty string, here is how you can do it before running this query:
$intLat = ($intLat === '') ? null : $intLat;
$query string, $intLat and $intLng are not quoted. So, when the variables are interpolated, it will be , NULL, NULL);.This works just fine for me:
INSERT INTO table VALUES ('', NULLIF('$date',''))
(first '' increments id field)
NULLIF(expr1, expr2) . See dev.mysql.com/doc/refman/5.7/en/…
If you don't pass values, you'll get nulls for defaults.
But you can just pass the word NULL without quotes.
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
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).
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.
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);
NULL. Also, you are using PHPs NULL (unquoted) keyword, which evaluates to an empty string in a string context, so again, you are assigning an empty string.Check the variables before building the query, if they are empty, change them to the string NULL
you can do it for example with
UPDATE `table` SET `date`='', `newdate`=NULL WHERE id='$id'
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;
