MySQL and PHP - insert NULL rather than empty string

Question

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

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng) VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', NULLIF('$intLat',''), NULLIF('$intLng',''))"; — Saghachi, Sep 03 '22 at 04:27

score 168 · Accepted Answer · edited May 11 '15 at 11:00

168

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

edited May 11 '15 at 11:00

Ram Sharma

8,676
7
43
56

answered Jan 06 '11 at 22:03

gen_Eric

223,194
41
299
337

14

This doesn't add a mysql NULL value. This set the attribute to the string "NULL" which is different from the mysql NULL value. I'm just saying that you wrote two different things, first is correct the second not so much I think. – G4bri3l Feb 21 '14 at 09:03
28

@G4bri3l: In the `$query` string, `$intLat` and `$intLng` are not quoted. So, when the variables are interpolated, it will be `, NULL, NULL);`. – gen_Eric Feb 21 '14 at 14:49
2

Oh yeah I see that now. Nice! Thanks for taking the time to point it out to me ;) – G4bri3l Feb 22 '14 at 13:38
1

@G4bri3l: No prob. I'm here to help :) – gen_Eric Feb 24 '14 at 15:04
@RocketHazmat What about if I have a code like this `$sql="INSERT INTO tablename VALUES ($field1, '$field2', '$field3', '$field4', '$field5', '$field6')"`? `$field3` and `'$field5'` might be empty strings. – candlejack Mar 29 '15 at 04:51
3

@alessadro: This question is very old. If you are concatenating variables into a SQL query like that, then you are doing it wrong! Please switch to using prepared statements in either PDO or MySQLi. – gen_Eric Mar 30 '15 at 03:26
empty() check will also consider 0 input as empty. This can be wrong for integers (e.g., minimum storage temperature = 0 and it is valid). if($value == ""){$value = NULL;} is better. Also, $value = "NULL" is not good either - it will replace empty string values with "NULL" text. – bbe Sep 04 '16 at 12:45
@bbe: While I agree that `empty()` may not be the best here, `$value == ''` doesn't fix the issue. `if(0 == '')` evaluates to `TRUE`, so that won't help here. Also, I am *not* setting the values to `"NULL"` text. I'm building the SQL query (`$query`), so that it will insert a `NULL` value into the database. – gen_Eric Sep 06 '16 at 14:04
this answer isn't accurate , adding NULL into the query becomes empty in MYSQL – hatched May 17 '20 at 13:13
How did this answer get so many upvotes... You can't insert the string "NULL" to make it null. Then it's not really null, it has a value which is the string "NULL." Adding empty quotes won't insert null either. It will insert an empty string. And using the keyword ```null``` in php will also insert an empty string. To insert NULL in a row, you simply don't insert anything, like this: ```INSERT INTO tablename VALUES ();``` and make sure you have that column set to nullable in sql. – AMR Nov 26 '21 at 21:38
@AMR if you leave it empty, PHP throws the following error: "Invalid datetime format: 1292 Incorrect datetime value: '' for column" – Marco Floriano Aug 10 '23 at 14:29

score 27 · Answer 2 · edited Apr 20 '18 at 09:28

27

This works just fine for me:

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

(first '' increments id field)

edited Apr 20 '18 at 09:28

SaschaM78

4,376
4
33
42

answered Dec 08 '16 at 13:45

Jesenko Sokoljak

271
3
4

1

NULLIF takes 2 parameters `NULLIF(expr1, expr2)` . See http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_nullif – Roy Hinkley Jan 19 '17 at 15:57
I think this is better because you can do in just one line – hiddeneyes02 Jun 02 '18 at 12:16
Not so simple with prepared statements. – MrrMan Jun 14 '20 at 19:51

score 18 · Answer 3 · answered Jan 06 '11 at 22:04

18

If you don't pass values, you'll get nulls for defaults.

But you can just pass the word NULL without quotes.

answered Jan 06 '11 at 22:04

dkretz

37,399
13
80
138

9

He'll only get NULL for default, if the table is set up that way. – gen_Eric Jan 06 '11 at 22:06
2

To me that's what "optional" means. – dkretz Jan 06 '11 at 22:28

score 13 · Answer 4 · edited Jun 09 '16 at 08:24

13

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

edited Jun 09 '16 at 08:24

Louis

2,854
2
19
24

answered May 06 '16 at 08:39

Shahid Sarwar

1,209
14
29

No need for quotes around the "NULL". – Louis Jun 09 '16 at 08:24

score 4 · Answer 5 · answered Oct 10 '13 at 14:32

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 column defined integers, the other solution it works fine, but for text fields, I had to break up the query as above into a string concatenation, like radhoo did above. $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (". "NULL". ", ".$val2.")"; — Brian, Jul 10 '18 at 22:41

score 2 · Answer 6 · answered Mar 08 '14 at 09:18

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.

score 2 · Answer 7 · edited Oct 15 '14 at 20:23

2

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

edited Oct 15 '14 at 20:23

Josiah

3,008
1
34
45

answered Jan 06 '11 at 22:08

sikas

5,435
28
75
120

2

$intLat, and $intLng are the optional ones. – gen_Eric Jan 06 '11 at 22:09
This will not work. There are quite a few things wrong with this code snippet (apart from using the wrong variables, as Rocket pointed out). Because of the precedence of the ternary operator, you'll need parentheses around the expression. But crucially, you are still surrounding the column value in single quotes, so you are back to square one, assigning a string, not a database `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_. – MrWhite Apr 21 '16 at 11:10

bernte · Answer 8 · 2013-08-02T14:59:16.353

1

you can do it for example with

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

edited Aug 02 '13 at 14:59

answered Jul 12 '13 at 19:19

bernte

1,184
2
19
34

score 1 · Answer 9 · answered Jan 06 '11 at 22:04

1

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

answered Jan 06 '11 at 22:04

profitphp

8,104
2
28
21

score 0 · Answer 10 · answered Mar 16 '22 at 12:16

0

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;

answered Mar 16 '22 at 12:16

ShellX3

51
8

MySQL and PHP - insert NULL rather than empty string

10 Answers10

Linked

Related