Can't set NULL default value

chris.cavage

My DB is set to accept null values for this field in particular, but as I coded the below script, the default value entered is '0' and not null.

    var editor_todos = new $.fn.dataTable.Editor({
    ajax: 'php/table.user_todos.php?user_id=' + user_id,
    table: '#user_todos',
    fields: [
         {
            "label": "Assign To Staff:",
            "name": "assigned_to_user_id",
            "type": "select2",
            "opts": {
                "ajax" { my ajax url code and script },
                "allowClear": true,
                "placeholder": {
                    "id": "",
                    "text": "(you)"
                }
            }

Any idea why the default is not being saved as NULL instead of 0, as it is now? I'm wondering if it has something to do with select2.

allan

If you have a look at the "Headers" section of the Ajax request that is being sent to the server in your browser's Network inspector - could you show me the parameters that are being sent please?

Thanks,
Allan

tangerine

I suspect that you may be running into a MySQL scenario. Sending '0' or an empty value will not result in a default of null.
Use "ifEmpty(null)" as a formatter for the field(s) in question.

chris.cavage

Hey Allan: here's the data from the form that's being sent:

action: create
data[0][location_id]: 
data[0][status_id]: 0
data[0][assigned_to_user_id]: 
data[0][due_date]: Wed, Feb 13 2019
data[0][assigned_to_contact_id]: 
data[0][text]: test
data[0][inserted_by_user_id]: 21
data[0][company_id]: 1

In the past, if I've submitted something like this to my DB, it does
insert assigned_to_contact_id as NULL. In this insert, location_id does enter as NULL. assigned_to_user_id and assigned_to_contact_id enter as 0. Both of these are using the select2.

location_id for right now is just a text input.

chris.cavage

Tangerine, this works per your suggestion:

         Field::inst( 'assigned_to_contact_id' )
         ->setFormatter( Format::ifEmpty( null ) )
         ->getFormatter( Format::ifEmpty( null ) ),

I have to use the formatter in this way for any field that is using the select2 plugin. The NULL values are entered in my DB instead of 0 values.

If you understand why this is necessary, I'd be curious to know! Thank you.

tangerine

I don't use select2, so I can't say if it makes a significant effect in this context.
I used to get problems with null defaults in MySQL, and from what I remember any value supplied (other than null) will not default. "0" is regarded as a legitimate value, and less obviously "" (empty) is also regarded as legitimate. You have to explicitly submit null, or not submit the field at all.
My memory might not be perfect - I recommend you check it out for yourself!

EDIT: There's also the PHP factor - "0" is considered empty.

allan

Yup - Editor submits the data from the form as HTTP parameters. They are basically just strings with zero typing information, so if you want an empty string to appear as null, then you need to use a formatter for it.

Allan

chris.cavage

Oh, I see! Thank you! That all makes sense. I understand. Great support!