Buy Now
Sign in

Not saving NULL in to DB

C
ctrlmedia
Hi,
I have a connection which uses a chronoform to save to add records to a database.
Everything works fine but the empty field saves as an empty string as far as I can make out. NULL is not in the table if the field is blank.

The DB column looks like this:
Name - Type - Collation - Null - Default  
priority - varchar(2) - utf8_general_ci - Yes - NULL    



The debug info on the save looks like this:

[save_new_issue] => Array
(
[data] => Array
(
[notes] => zero hours notes
[priority] =>
[title] => Zero Hours Contract




The actual save looks like:
INSERT INTO `table_name` (`notes`, `priority`, `title`) values ( 'zero hours notes', '', 'Zero Hours Contract');





I have also added this function which works, if I change NULL to 5 the value changes to 5.
if($this->data["Issues"]["priority"] == ""){$this->data["Issues"]["priority"] = NULL;}


When the record saves it leaves the field "priority" blank rather than "NULL".
I was wondering if anyone had any suggestions?

Kind Regards
admin
Hi Mark,

Just unset the field from the save data array if its empty before the "save data" function:

if($this->data["Issues"]["priority"] == ""){unset($this->data["Issues"]["priority"]);}

does that work ?

Best regards,
Max
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
C
ctrlmedia
HI Max,
That has done the trick, thanks again for your help!

Kind Regards
healyhatman
This doesn't work consistently for me.
if($this->data["client_id"] == "") {​
​ unset($this->data["client_id"]);​
​}​
​​
​if($this->data["client_name"] == "") {​
​ unset($this->data["client_name"]);​
​}
For some reason if either is empty it unsets them from the data array, but it still send the client_name to the save_data action. The client_id is correctly unset.
Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman
admin
Hi healyhatman,
Yes, assuming you run this code in a PHP action, the solution is to do the following:
$new_data = $this->data;​
​if($new_data["client_id"] == "") {​
​unset($new_data["client_id"]);​
​}​
​​
​return $new_data;
Then use {var:php_name} as the data provider of the "Save data" action.
Best regards,
Max
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!