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