[benhawk]

Hi,

Im getting problems when trying to set columns (particularly foreign key ones) as null instead of blank in my database.

I have two tables being

Booking and Archive.

When a booking is complete or deleted it is placed in the archive table.

The problem is there are a few fields which may be empty including two foreign key fields.

If there value is empty I need null to be placed in there field.

Currently the system attempts to add blank space into the fields which causes a foreign key contraint error show below.

Runing Booking Function test 123 fake streed Invalid query: Cannot add or update a child row: a foreign key constraint fails (`taxi booking system`.`archive`, CONSTRAINT `archive_ibfk_9` FOREIGN KEY (`Driver_ID`) REFERENCES `drivers` (`ID`)) Whole query: INSERT INTO Archive (ID, From_Address, From_Lat,	From_Lng, To_Address, To_Lat, To_Lng, Driver_ID, Customer_ID, Date, Status, Reason, Created, Special_Request, Complete) VALUES ('8', '123 Fakestreet', '50.822563', '-1.071819', '', '', '', '', '', '0000-00-00 00:00:00', '1', '', '2012-01-11 14:50:03', '', '1');

DriverID and CustomerID are both forigen keys in the table however in this case they are blank and causing the error.

I have tried checking each variable and if its empty placing "NULL" into it, however the database seems to still accept that as a string and gives the same error

Can anyone tell me how to correctly add NULL to any fields which are empty?

Thanks in advance

Ben Gibson

[webdesigner93]

benhawk, on 11 January 2012 - 03:36 PM, said:

Hi,

Im getting problems when trying to set columns (particularly foreign key ones) as null instead of blank in my database.

I have two tables being

Booking and Archive.

When a booking is complete or deleted it is placed in the archive table.

The problem is there are a few fields which may be empty including two foreign key fields.

If there value is empty I need null to be placed in there field.

Currently the system attempts to add blank space into the fields which causes a foreign key contraint error show below.

Runing Booking Function test 123 fake streed Invalid query: Cannot add or update a child row: a foreign key constraint fails (`taxi booking system`.`archive`, CONSTRAINT `archive_ibfk_9` FOREIGN KEY (`Driver_ID`) REFERENCES `drivers` (`ID`)) Whole query: INSERT INTO Archive (ID, From_Address, From_Lat,	From_Lng, To_Address, To_Lat, To_Lng, Driver_ID, Customer_ID, Date, Status, Reason, Created, Special_Request, Complete) VALUES ('8', '123 Fakestreet', '50.822563', '-1.071819', '', '', '', '', '', '0000-00-00 00:00:00', '1', '', '2012-01-11 14:50:03', '', '1');

DriverID and CustomerID are both forigen keys in the table however in this case they are blank and causing the error.

I have tried checking each variable and if its empty placing "NULL" into it, however the database seems to still accept that as a string and gives the same error

Can anyone tell me how to correctly add NULL to any fields which are empty?

Thanks in advance

Ben Gibson

mmm are u putting quotes around NULL if so thats ur problem

$sql = mysql_query("INSERT INTO `table` (`field_1`,`field_2`) VALUES('Hello',NULL)");

field_2 is the NULL value don't use quotes around NULL or it will be treated as a string

[benhawk]

webdesigner93, on 11 January 2012 - 04:05 PM, said:

mmm are u putting quotes around NULL if so thats ur problem

$sql = mysql_query("INSERT INTO `table` (`field_1`,`field_2`) VALUES('Hello',NULL)");

field_2 is the NULL value don't use quotes around NULL or it will be treated as a string

Hi,

Thanks for the help

I am doing my statement like so

unction archive() {
	    

	    	
$query = sprintf("INSERT INTO Archive (ID, From_Address, From_Lat, From_Lng, To_Address, To_Lat, To_Lng, Driver_ID, Customer_ID, Date, Status, Reason, Created, Special_Request, Complete) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s',  '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($this->id), 
mysql_real_escape_string($this->fromAddress), 
mysql_real_escape_string($this->fromLat), 
mysql_real_escape_string($this->fromLng), 
mysql_real_escape_string($this->toAddress), 
mysql_real_escape_string($this->toLat), 
mysql_real_escape_string($this->toLng), 
mysql_real_escape_string($this->driverID), 
mysql_real_escape_string($this->customerID),  
mysql_real_escape_string($this->date), 
mysql_real_escape_string($this->status), 
mysql_real_escape_string($this->reason),
mysql_real_escape_string($this->created), 
mysql_real_escape_string($this->specialRequest), 
mysql_real_escape_string($this->complete));
	    	
	    	
$result = mysql_query($query);

Im guessing mysql_real_escape_string is in someway preventing NULL from going in without being a string.

[webdesigner93]

benhawk, on 11 January 2012 - 04:15 PM, said:

Hi,

Thanks for the help

I am doing my statement like so

unction archive() {
	    

	    	
$query = sprintf("INSERT INTO Archive (ID, From_Address, From_Lat, From_Lng, To_Address, To_Lat, To_Lng, Driver_ID, Customer_ID, Date, Status, Reason, Created, Special_Request, Complete) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s',  '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($this->id), 
mysql_real_escape_string($this->fromAddress), 
mysql_real_escape_string($this->fromLat), 
mysql_real_escape_string($this->fromLng), 
mysql_real_escape_string($this->toAddress), 
mysql_real_escape_string($this->toLat), 
mysql_real_escape_string($this->toLng), 
mysql_real_escape_string($this->driverID), 
mysql_real_escape_string($this->customerID),  
mysql_real_escape_string($this->date), 
mysql_real_escape_string($this->status), 
mysql_real_escape_string($this->reason),
mysql_real_escape_string($this->created), 
mysql_real_escape_string($this->specialRequest), 
mysql_real_escape_string($this->complete));
	    	
	    	
$result = mysql_query($query);

Im guessing mysql_real_escape_string is in someway preventing NULL from going in without being a string.

mmm nah the problem is all these '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' have quotes around theme, the spot the null value is gonna be placed at does not need to have quotes otherwise it will be treated as a string

[benhawk]

webdesigner93, on 11 January 2012 - 04:34 PM, said:

mmm nah the problem is all these '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' have quotes around theme, the spot the null value is gonna be placed at does not need to have quotes otherwise it will be treated as a string

This doest seem to work either, they only way I have managed to get it to work so far is to place 'NULL' in where the %s are, however this removes the ability to add a value if there is one