Can't set NULL default value

Can't set NULL default value

chris.cavagechris.cavage Posts: 46Questions: 12Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

    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.cavagechris.cavage Posts: 46Questions: 12Answers: 0
    edited February 2019

    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.cavagechris.cavage Posts: 46Questions: 12Answers: 0

    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.

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    edited February 2019

    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.

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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.cavagechris.cavage Posts: 46Questions: 12Answers: 0

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

This discussion has been closed.