MySQL and PHP - insert NULL rather than empty string
Solution 1
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
This works just fine for me:
INSERT INTO table VALUES ('', NULLIF('$date',''))
(first ''
increments id field)
Solution 3
If you don't pass values, you'll get nulls for defaults.
But you can just pass the word NULL without quotes.
Solution 4
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
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).
Related videos on Youtube
user547794
Updated on March 17, 2022Comments
-
user547794 about 2 years
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);
-
gen_Eric over 13 yearsHe'll only get NULL for default, if the table is set up that way.
-
gen_Eric over 13 years$intLat, and $intLng are the optional ones.
-
dkretz over 13 yearsTo me that's what "optional" means.
-
G4bri3l about 10 yearsThis 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.
-
gen_Eric about 10 years@G4bri3l: In the
$query
string,$intLat
and$intLng
are not quoted. So, when the variables are interpolated, it will be, NULL, NULL);
. -
G4bri3l about 10 yearsOh yeah I see that now. Nice! Thanks for taking the time to point it out to me ;)
-
gen_Eric about 10 years@G4bri3l: No prob. I'm here to help :)
-
candlejack about 9 years@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. -
gen_Eric about 9 years@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.
-
MrWhite about 8 yearsThis 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 PHPsNULL
(unquoted) keyword, which evaluates to an empty string in a string context, so again, you are assigning an empty string. -
Louis almost 8 yearsNo need for quotes around the "NULL".
-
bbe over 7 yearsempty() 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.
-
gen_Eric over 7 years@bbe: While I agree that
empty()
may not be the best here,$value == ''
doesn't fix the issue.if(0 == '')
evaluates toTRUE
, 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 aNULL
value into the database. -
Roy Hinkley over 7 yearsNULLIF takes 2 parameters
NULLIF(expr1, expr2)
. See dev.mysql.com/doc/refman/5.7/en/… -
hiddeneyes02 almost 6 yearsI think this is better because you can do in just one line
-
Brian almost 6 yearsFor 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.")";
-
hatched about 4 yearsthis answer isn't accurate , adding NULL into the query becomes empty in MYSQL
-
MrrMan almost 4 yearsNot so simple with prepared statements.
-
Loganathan Natarajan over 2 yearsawesome, it works
-
AMR over 2 yearsHow 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.