MySQL and PHP - insert NULL rather than empty string

I have 开发者_JS百科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);

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)";

This works just fine for me:

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

(first '' increments id field)

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);

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;