Page 1 of 1

MySQL: Automatic way to set empty values to NULL?

Posted: Tue Apr 17, 2007 6:31 pm
by batfastad
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...

Code: Select all

$sql_query = "INSERT INTO adbookings VALUES ('','$company_id','$ad_agency_email','$ad_agency_contact_name');";
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...

Code: Select all

$sql_query = "INSERT INTO adbookings VALUES ('','$company_id','','');";
So I can understand why this happens.

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'";
}
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...

Code: Select all

$sql_query = str_replace ( "''", "NULL", $sql_query);
$sql_query = str_replace ( ",,", ",NULL,", $sql_query);
... 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

Posted: Tue Apr 17, 2007 7:42 pm
by volka
Take a look at

Code: Select all

$arr = array(
		'fieldName1'=>'value1',
		'fieldName2'=>'',
		'fieldName3'=>'',
		'fieldName4'=>'value4'
	);
	
$arr = array_filter($arr);
print_r(array_keys($arr));
print_r(array_values($arr));

Posted: Wed Apr 18, 2007 3:12 am
by JayBird
I do this mostly

Store all the field names in an array like volka showed above, then i use the following bit of code

Code: Select all

foreach ($arrayWithFields as $key => $value)
{
    $sql .= (!empty($sql)?', `':'`') . $key .'` = ' . (!is_numeric($value) && empty($value)?'NULL':"'$value'");
}

$sql = 'INSERT INTO `' . $table_name . '` SET ' . $sql;

Posted: Wed Apr 18, 2007 6:11 am
by timvw
I'd probably add mysql_escape_string($value) instead of the unprepared $value...

Posted: Wed Apr 18, 2007 11:42 am
by RobertGonzalez
This is more of a code issue than a database issue, no?