MySQL: Automatic way to set empty values to NULL?
Posted: Tue Apr 17, 2007 6:31 pm
Hi everyone
In my DB table I have most fields set to allow NULL values - apart from autoenters and a couple of important ID fields.
In all the fields that are set to allow NULL values, I've also set the default value of those fields to NULL.
The problem I'm coming across though is when I do an INSERT command like the following...
If $ad_agency_email or $ad_agency_contact_name are empty values from my form, then that empty value zero-length string gets inserted into my DB.
Despite the default field value being set to NULL in MySQL.
There's a logical reason for this is of course... if those variables are empty, then the query is in effect this...
So I can understand why this happens.
The solution I've found to this is to do this for each of the variables...
So where there is a value it's put in quotes so it's inserted as a string. But where there's no value it gets inserted as NULL (rather than the actual word 'NULL').
This works but it's not fun if I've got 80 variables... needs hundreds of lines of code just to do it.
My question is this...
Is there an automatic way I can run through my query just before it's executed and have any empty values converted to NULL?
I thought of doing the following...
... to replace any occurences of '' (2 single quote marks where there is no value) with NULL
And also to replace 2 commas in a row where the variable was meant to be a number and therefore not surrounded by single-quote marks.
Now in theory this works (apart from variables at the start/end of the query next to the brackets.
But I was wondering if there's a better way to do this?
Surely there must be!
Thanks in advance for your advice
Ben
In my DB table I have most fields set to allow NULL values - apart from autoenters and a couple of important ID fields.
In all the fields that are set to allow NULL values, I've also set the default value of those fields to NULL.
The problem I'm coming across though is when I do an INSERT command like the following...
Code: Select all
$sql_query = "INSERT INTO adbookings VALUES ('','$company_id','$ad_agency_email','$ad_agency_contact_name');";Despite the default field value being set to NULL in MySQL.
There's a logical reason for this is of course... if those variables are empty, then the query is in effect this...
Code: Select all
$sql_query = "INSERT INTO adbookings VALUES ('','$company_id','','');";The solution I've found to this is to do this for each of the variables...
Code: Select all
if (strlen($ad_agency_email) < 1) {
$ad_agency_email = NULL;
} else {
$ad_agency_email = "'$ad_agency_email'";
}This works but it's not fun if I've got 80 variables... needs hundreds of lines of code just to do it.
My question is this...
Is there an automatic way I can run through my query just before it's executed and have any empty values converted to NULL?
I thought of doing the following...
Code: Select all
$sql_query = str_replace ( "''", "NULL", $sql_query);
$sql_query = str_replace ( ",,", ",NULL,", $sql_query);And also to replace 2 commas in a row where the variable was meant to be a number and therefore not surrounded by single-quote marks.
Now in theory this works (apart from variables at the start/end of the query next to the brackets.
But I was wondering if there's a better way to do this?
Surely there must be!
Thanks in advance for your advice
Ben