Forums

Hi,

I have an update function that updates a column in my database.
When I do the update the values that were NULL and are not being updated with a value are just turned blank (empty string - not NULL).

On update I need NULL to be passed (or totally unset) if the value is empty. I don't know where to do this in the loop function?

Any pointers would be much appreciated.

Kind Regards
Mark

Hi Mark,

You probably need to 'unset' any empty values in the $form->data array before the DB Save, that should prevent the NULL values being over-written. (You could try setting them to NULL but I suspect that will just save the 'NULL' string and not a NULL value.)

Bob

Hi Bob,
Thanks for your reply.
This is what I have been trying but I am unsure where and how to do this in this scenario.

I have been putting a function in the loop body which tries to get the value and if its empty unset it but I cant seem to get it to work.

Kind Regards
Mark

Hi Mark,

What is the code you are using?

Bob

Hi Bob,
Thanks again for your help.

The code I used would not work because I am thinking that I need to put the code in the "Loop Body" but I am unsure how to target $this row in the loop with PHP and then say unset if empty.

So what I mean is, I can target the row using this "{var:save_priority_loop.row}" but I cannot use PHP with this as PHP works before hand so how do I unset this row?

Or am I looking at it in the wrong way?


Regards
Mark

Hi Mark,

You can call a PHP function before the save data function and do any processing there.

if(empty($this->get("save_priority_loop.row.field"))){
$this->set("save_priority_loop.row.field", null);
}

Best regards,
Max

Hi Max,
Thanks for your help on this.

To make the above code work I had to remove ".field" I could then target the value I was after so the answer above does seem to work. If I change NULL to "0" all the empty values go to "0".

The problem is when I ask for them to be set to NULL they still go in to the database as blank values rather than NULL.

Is there any way I can unset the empty ones?
I have tried the below which didn't work.

unset($this->get("save_priority_loop.row"));

Kind Regards
Mark

Hi Mark,

Using this line does not work:

$this->set("save_priority_loop.row.field", null);

?

Best regards,
Max

Hi Max,

No, I had to remove ".field" for it to work.

When I remove .field I can get it to set the value to anything I want so I can see it works.
The database still saves as a blank string rather than NULL though when setting it as NULL.

Kind Regards
Mark

Hi Max,
Just to confirm - in the loop body I have these two functions:
{fn:change_priority_null1}
{fn:update_priority}

The first function is:

if($this->get("save_priority_loop.row") == ""){
$this->set("save_priority_loop.row", NULL);
}

And the second function is the save to database function.

Kind Regards
Mark

Just another addition.

I actually managed to get the empty values to be unset (removed from array).
I then realised that this will not work because if someone sets the value from 3 to empty it will not get passed to the DB (empty values get unset from the array before save) and therefore 3 will remain in the DB.

I actually need NULL to be sent to the DB but I don't seem to be able to get it to work. The database is setup correctly I have set the values to NULL as per the code above (which makes them blank) - is there anything else I may have missed?

Regards
Mark

Hi Mark,

What is the data provider or your loop ? usually the loop "row" has multiple fields under it and you need to test that, that is why I have ".field" in my code above.

You may also use a PHP function, get the whole row values, check all of them and ignore any empty ones, then return a new data set and use {var:php_name} as the data provider for the save data function.

Best regards,
Max

Hi Max,

I have just made a very basic one field form with CFv6.

The DB column has been set up to have NULL set to yes and default set to NULL

When I click save, the modified column has NULL but the field I created has nothing in (blank string) when it should default to NULL?


This to me says it is something wrong elsewhere?

Kind Regards
Mark

Hi Mark,

If the field is available in the data array to be saved and its empty then an empty string will be saved, this is correct.

You need to remove the field from the data set before the save occurs, as I suggested before you can use a PHP function to filter the dataset and provide another one with non empty values only.

Best regards,
Max

Hi Max,

I really appreciate your help!

I thought if you saved an empty string it should be interpreted as the default value in the DB, which in this case is NULL.
Obviously this is not the case so I have had to re-work this because I was building an ordering column.

I wanted to allow people to order the rows as they wanted - some of which would not be ordered at all (NULL) - I have made a workaround where the DB puts a default value of 99 in and then when the inputs load I have removed any values that are 99 so that they are not displayed in the table.

When the table is updated any blank values are re-set to 99 and saved.

Is there a better way to do this?

Kind Regards
Mark

Hi Mark,

The default values are used when the field is not available in the data set to be saved.

Did you try my suggestion of processing the data set before saving and dropping any empty string values ? that's the best possible solution for this issue, it is not a good idea to set the table fields to unreal values like 99 or whatever.

Best regards,
Max

Hi Max,

Yeah I got that working where I would remove any blank values from the data set before saving (therefore leaving NULL in the DB) but the issue I have is if the value was 5 (for example) and someone removed the value (because it was no longer a priority) then NULL would not be saved to the DB.

In this example what would happen is a blank string would be passed to the data set then removed totally (becuase it was blank) and so 5 would remain in the DB.

Unless I have misunderstood you suggestion?

Kind Regards
Mark

Hi Mark,

What happens when you set the field value in the data set to "NULL" and set the table field to accept "NULL" (null enabled) ?

Best regards,
Max

Hi Max,

$this->data["priority"] = NULL;
Saves an empty string to the database

$this->data["priority"] = "NULL";
Saves the string "NULL" to the database.

So the default NULL value is never there?

Kind Regards
Mark

Hi Mark,

Yes, the default field value is only used when the field is not provided in the data set to be "inserted", but here you are trying to update, and so the value NULL should be passed.

I have found the source of the issue, please send me a message using the contact us page and I will send you a patch file.

Best regards,
Max