MySQL: Automatic way to set empty values to NULL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL: Automatic way to set empty values to NULL?

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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));
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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;
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I'd probably add mysql_escape_string($value) instead of the unprepared $value...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

This is more of a code issue than a database issue, no?
Post Reply