Menu
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); 
945
In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.
You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows. INSERT INTO yourTableName(yourColumnName) values(NULL); To understand the above syntax, let us first create a table.
is_null() The empty() function returns true if the value of a variable evaluates to false . This could mean the empty string, NULL , the integer 0 , or an array with no elements. On the other hand, is_null() will return true only if the variable has the value NULL .
However, it will throw an error of unique constraint if you use an empty string here. So, NULL is better. An empty string is useful when the data comes from multiple resources. NULL is used when some fields are optional, and the data is unknown.
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)"; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
