passing a null value to mysql database
A user fills out a form and if they choose to not fill out a field that is not required php does this:
if($_SESSION['numofchildren']=="") $_SESSION['numofchildren']=null; But when I use the session variable in a mysql query, the result is not null, but is 0. The column is a tinyint(4) that allows NULL. Why am I getting a 0 instead of NULL?
8 Answers 8
Probably because PHP doesn’t convert ‘null’ into ‘NULL’. You are probably just inserting an empty value.
You probably have the default for the column set to ‘0’, and that means that it will insert a 0 unless you specify a number or NULL
INSERT INTO TABLE ('Field') (NULL) To fix this, check for Null Values before you do the query.
foreach($values as $key => $value) < if($value == null) < $values[$key] = "NULL"; >> I have a feeling that prepared statements will have the foresight to do this automagically. But, if you are doing inline statements, you need to add a few more things.
MySQL values must have quotes around them, but Nulls don’t. Therefore, you are going to need to quote everything else using this
foreach($values as $key => $value) < if($value == null) < $values[$key] = "NULL"; >else < // Real Escape for Good Measure $values[$key] = "'" . mysql_real_escape_string($value) . "'"; >> Then, when you create the statement, make sure to not put quotes around any values
$SQL = "INSERT INTO TABLE (Field) VALUES(".$values['field'].")"; $SQL = "INSERT INTO TABLE (Field) VALUES("Test Value")"; $SQL = "INSERT INTO TABLE (Field) VALUES(NULL)"; insert NULL in sql query using PHP
I want to build query using two array 1 for column and another for value. I have 3 integer columns in first array for which I don’t want to store 0 as default so i want to set NULL, I want to check if any value entered for it, if not entered i want to set NULL for it . But when i create query i cant see NULL, i just see null replaced by blank. Seems like PHP is stripping out the NULL word ?
$set_null = array("asst_phone","mailing_zip","other_zip"); foreach($columns as $key=>$col) < if(in_array($col,$set_null) && $values[$key] == "")< $val[$key] = NULL; >else $val[$key] = "'".$values[$key]."'"; > $query_vals = ""; foreach($val as $qval) < if($qval == "") $query_vals .= NULL; $query_vals .= $qval; >echo $query_vals; INSERT INTO zoho_contacts (`contactid`,`smownerid`,`contact_owner`,`lead_source`,`first_name`,`last_name`,`accountid`,`account_name`,`email`,`title`,`department`,`phone`,`home_phone`,`other_phone`,`fax`,`mobile`,`date_of_birth`,`assistant`,`asst_phone`,`reports_to`,`smcreatorid`,`created_by`,`modifiedby`,`modified_by`,`created_time`,`modified_time`,`mailing_street`,`other_street`,`mailing_city`,`other_city`,`mailing_state`,`other_state`,`mailing_zip`,`other_zip`,`mailing_country`,`other_country`,`description`,`email_opt_out`,`skype_id`,`salutation`,`add_to_quickbooks`,`secondary_email`) values ('418176000000052001','418176000000047003','','','','','418176000000047027','','','','','02164 25452132','','','','9798659821','','',,'','418176000000047003','','418176000000047003','','','','','','','','',''. '','','','','','Mr.','','') Proper use of NULL in mysql query from php
Each SQL error has a number. What is the error number you’ve got (if not two numbers)? And which part of the error message (which you have posted), is not clear to you?
2 Answers 2
NULL is a specific value, not a string literal. It should be passed to query directly, i.e.
INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`) VALUES ('2013-08-28 12:30:00', NULL, NULL); that means your PHP code should handle that and not enclose NULL-s:
$timeFormatAndNull = function ($format) < return function($time) use ($format) < $time = strtotime($time); return $time ? strftime($format, $time) : 'NULL'; >; >; $operationalLocalDate = function($arrivals, $callback) < return function($i, $date) use ($arrivals, $callback) < return $callback( $arrivals[$i]["operationalTimes"][$date]["dateLocal"]) ); >; >; $formatTime = $operationalLocalDate( $arrivals $timeFormatAndNull("'%Y-%m-%d %H:%M:%S'") ); $query = sprintf( "INSERT INTO `Schedule` (`ETA`,`STA`,`ATA`) VALUES (%s, %s, %s);" , $formatTime($i, "estimatedGateArrival") , $formatTime($i, "scheduledGateArrival") , $formatTime($i, "actualGateArrival") ); Actually as the question states, NULL is also the default value, so the more correct way — in my eyes — is to not pass the NULL values with the INSERT at all as they are not needed (I can not imagine this was the reason of the downvote though). But this would need a different way to create the SQL query.
The reason for having that error message is because you are passing a string of NULL (which is impossible to convert into datetime format) in the datetime datatype.
When you want the value of column STA and ATA to be NULL , pass NULL value without single quotes. The single quote around the value makes it a literal string.
INSERT INTO `Schedule` (ETA, STA, ATA) VALUES ('2013-08-28 12:30:00', NULL, NULL) As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.
MySQL and PHP — insert NULL rather than empty string
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’,»))»;
10 Answers 10
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 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: In the $query string, $intLat and $intLng are not quoted. So, when the variables are interpolated, it will be , NULL, NULL); .
@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.
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 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.»)»;
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); 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.
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 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;