Table suddenly stopped updating, and Editor window won't close

Stacey1134

Ok, this code has worked fine for a month now, and then last week it suddenly stopped updating!. I have went over everything I can think of and I am at a loss. I can load the table, and open the editor window. When I make an update and call submit, I see the little icon at the top of the editor screen and that's all the table is not updated. To make things more weird, most of the current entries and updates were made with the SAME module. I installed the debugger and got no errors but the suggestion to upgrade to a newer version (which I did). Am I missing something simple ( i hope ) Thanks' for anyone's insight.

server side script (php): I streamlined this one for testing and debugging, but it is still not updating:

<?php

// DataTables PHP library
include( "Editor-PHP/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchBuilderOptions;   


/*
 * Example PHP implementation used for the join.html example
 */

Editor::inst( $db, 'PrisonFacilityListing', 'Facility_ID' )
    ->fields(
        Field::inst( 'Facility_ID' ),
        Field::inst( 'Facility_Type' )->validator( 'Validate::notEmpty')
            ->options( Options::inst()
            ->table( 'FacilityTypes' )
            ->value( 'Type' )
            ->label( 'Type' )
            )           
            ->searchBuilderOptions( SearchBuilderOptions::inst()),        
        Field::inst( 'Prison_Name' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Address_1' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Address_2' ),
        Field::inst( 'Address_3' ),
        Field::inst( 'City' )->validator( 'Validate::notEmpty')
            ->searchBuilderOptions( SearchBuilderOptions::inst()),        
        Field::inst( 'State' )->validator( 'Validate::notEmpty')
            ->searchBuilderOptions( SearchBuilderOptions::inst()),        
        Field::inst( 'Zipcode' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Mailing_Notes' ),
        Field::inst( 'Phone_Number' ),
        Field::inst( 'Mail_Addr_1' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Mail_Addr_2' ),
        Field::inst( 'Mail_Addr_3' ),                                        
        Field::inst( 'Mail_City' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Mail_State' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Mail_Zip' )->validator( 'Validate::notEmpty'),
        Field::inst( 'Pkg_Addr_1' ),
        Field::inst( 'Pkg_Addr_2' ),                
        Field::inst( 'Pkg_Addr_3' ),                        
        Field::inst( 'Pkg_City' ),
        Field::inst( 'Pkg_State' ), 
        Field::inst( 'Pkg_Zip' ), 
        Field::inst( 'Alt_Addr_1' ),
        Field::inst( 'Alt_Addr_2' ),                
        Field::inst( 'Alt_Addr_3' ),                        
        Field::inst( 'Alt_City' ),
        Field::inst( 'Alt_State' ), 
        Field::inst( 'Alt_Zip' ), 
        Field::inst( 'Website' ),                
        Field::inst( 'DOC_ACR' ),
        Field::inst( 'eOnly' ),                       
        Field::inst( 'eProvider' )
            ->options( Options::inst()
            ->table( 'eProvider' )
            ->value( 'name' )
            ->label( 'name' )
        ),                                                                 
        Field::inst( 'EditedBy' )->validator( 'Validate::email' ),
        Field::inst( 'correspondence' ), 
        Field::inst( 'publications' ), 
        Field::inst( 'books' ),                         
        Field::inst( 'timestamp' )                               

    )
    ->debug(true)
    ->process( $_POST )
    ->json();

Stacey1134

I even made a standalone page, in hopes of finding the problem. Here is the relevant JS:

 $(document).ready(function() { 

    var editor = new $.fn.dataTable.Editor( {
        ajax: 'ajaxFacility.php',
        table: '#facilityTable',
        template: '#facForm',      
        display: 'bootstrap', 
        fields: [ {
                name:  "Facility_Type",
                type:  "select",                  
                className: 'full block'                    
            }, {                
                name: "Prison_Name",
                className: 'full block'
            }, {                
                name:  "Website",
                className: 'full block'                    
            }, {                
                name:  "Phone_Number",
                className: 'full block'                        
            }, {
                name:  "DOC_ACR",
                className: 'full block'    
            }, {                                                             
                name:  "Address_1",
                className: 'full block'                        
            }, {
                name:  "Address_2",
                className: 'full block'                        
            }, {
                name:  "Address_3",
                className: 'full block'                        
            }, {
                name:  "City",
                className: 'full block'                        
            }, {
                name:  "State",
                className: 'full block'                        
            }, {
                name:  "Zipcode",
                className: 'full block'    
            }, {
                name:  "Mailing_Notes",
                type: "quill",
                className: 'full block'                      
            }, {
                name:  "Mail_Addr_1",
                className: 'full block'                      
            }, {
                name:  "Mail_Addr_2",
                className: 'full block'                      
            }, {
                name:  "Mail_Addr_3",
                className: 'full block'                      
            }, {
                name:  "Mail_City",
                className: 'full block'                      
            }, {
                name:  "Mail_State",
                className: 'full block'                      
            }, {
                name:  "Mail_Zip",
                className: 'full block'                      
            }, {
                name:  "Pkg_Addr_1",
                className: 'full block'                      
            }, {
                name:  "Pkg_Addr_2",
                className: 'full block'                      
            }, {
                name:  "Pkg_Addr_3",
                className: 'full block'     
            }, {
                name:  "Pkg_City",
                className: 'full block'                      
            }, {                    
                name:  "Pkg_State",
                className: 'full block'                      
            }, {
                name:  "Pkg_Zip",
                className: 'full block'                      
            }, {
                name:  "Alt_Addr_1",
                className: 'full block'                      
            }, {
                name:  "Alt_Addr_2",
                className: 'full block'                      
            }, {
                name:  "Alt_Addr_3",
                className: 'full block'     
            }, {
                name:  "Alt_City",
                className: 'full block'                      
            }, {                    
                name:  "Alt_State",
                className: 'full block'                      
            }, {
                name:  "Alt_Zip",
                className: 'full block' 
            }, {
                name:  "eOnly",
                type:  "select",   
                    options: [
                        { label: "No", value: "No" },
                        { label: "Yes",value: "Yes" }
                    ],                    
                className: 'full block'                      
            }, {                    
                name:  "eProvider",
                type:  "select",                  
                className: 'full block'                      
            }, {
                name:  "correspondence",
                type:  "select", 
                    options: [
                    { label: "Physical Address", value: 1},
                    { label: "Mailing Address", value: 2 },
                    { label: "Books Pub. Address", value: 3},
                    { label: "Alternate Address", value: 4}
                ],                    
                className: 'full block' 
            }, {
                name:  "publications",
                type:  "select", 
                    options: [
                    { label: "Physical Address", value: 1},
                    { label: "Mailing Address", value: 2 },
                    { label: "Books Pub. Address", value: 3},
                    { label: "Alternate Address", value: 4}
                ],                     
                className: 'full block' 
            }, {
                name:  "books",
                type:  "select", 
                    options: [
                    { label: "Physical Address", value: 1},
                    { label: "Mailing Address", value: 2 },
                    { label: "Books Pub. Address", value: 3},
                    { label: "Alternate Address", value: 4}
                ],                     
                className: 'full block' 
            }, {
                name:  "timestamp",
                className: 'full block'                                                             
            }, {
                name: "EditedBy",
                type: "hidden"
            }

        ]
    } );

    var table = $('#facilityTable').DataTable( {
        serverSide: true,
        processing: true,
        language: {
        processing: '<i class="fa-duotone fa-gear fa-spin"></i> Data Loading...',    
        searchBuilder: {
            button: '<i class="fa-sharp fa-solid fa-magnifying-glass-arrow-right"></i> Refine Search' ,
            title: 'Choose Filter Options'
            }
        },
        buttons:[
            '<i class="fa-sharp fa-solid fa-magnifying-glass-arrow-right"></i> Refine Search' 
        ],
        ajax: {
            url: "ajaxFacility.php",
            type: "POST"
        }, 
        // ajax: "ajaxFacility.php",
        columns: [            
            { data: "Facility_ID" },
            { data: "Facility_Type" },
            { data: "Prison_Name" },                
            { data: "Address_1", visible: false },
            { data: "Address_2", visible: false },
            { data: "Address_3", visible: false },                
            { data: "City" },
            { data: "State" },
            { data: "Zipcode" , visible: false, searchable: false },
            { data: "Mailing_Notes", visible: false , searchable: false },
            { data: "Phone_Number", visible: false, searchable: false },
            { data: "Mail_Addr_1", visible: false , searchable: false },
            { data: "Mail_Addr_2", visible: false , searchable: false },
            { data: "Mail_Addr_3", visible: false , searchable: false },
            { data: "Mail_City", visible: false , searchable: false },
            { data: "Mail_State", visible: false , searchable: false },
            { data: "Mail_Zip", visible: false , searchable: false },
            { data: "Pkg_Addr_1", visible: false , searchable: false },
            { data: "Pkg_Addr_2", visible: false , searchable: false },
            { data: "Pkg_Addr_3", visible: false , searchable: false },                
            { data: "Pkg_City", visible: false , searchable: false },
            { data: "Pkg_State", visible: false , searchable: false },                                                
            { data: "Pkg_Zip", visible: false , searchable: false },
            { data: "Alt_Addr_1", visible: false , searchable: false },
            { data: "Alt_Addr_2", visible: false , searchable: false },
            { data: "Alt_Addr_3", visible: false , searchable: false },                
            { data: "Alt_City", visible: false , searchable: false },
            { data: "Alt_State", visible: false , searchable: false },                                                
            { data: "Alt_Zip", visible: false , searchable: false },
            { data: "Website", visible: false , searchable: false },
            { data: "DOC_ACR", visible: false , searchable: false }, 
            { data: "eOnly", visible: false , searchable: false }, 
            { data: "eProvider", visible: false , searchable: false },                                                                                
            { data: "EditedBy", visible: false , searchable: false },
            { data: "timestamp", visible: false , searchable: false }                 
        ],
        select: true,
        lengthMenu: [[5,10,20,-1],[5,10,20,"All"]],
        // loadingIndicator: true,   
        pageLength: 5,
        autoWidth: true,
        order: [[ 7, 'asc' ], [ 1, 'asc' ]]
    } );

Stacey1134

    // Display the buttons
    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor, text: 'View/Edit', 
            formTitle: 'View or Update Facility Record',
            titleAttr: 'View or Update Facility Record',
            formButtons: [
                {
                    text: 'Cancel',
                    action: function () { this.close(); },
                    className: 'btn btn-secondary'
                }, {
                    text: 'Update',
                    action: function () { this.submit(); },
                    className: 'btn btn-primary'
                }
            ]   
        }, 
        { extend: "remove", editor: editor, text: 'Remove' },        
        { extend: "searchBuilder", 
            config: { 
                columns: [1,6,7],                                        
            }, 
            className: 'btn btn-warning', 
            text: '<i class="fa-sharp fa-solid fa-magnifying-glass-arrow-right"></i> Refine Search' 
        }

    ] );


    editor.on('open', function(e, mode, action) {
        $('div.modal-dialog', editor.displayNode()).addClass('modal-xl');
    });   

    editor.on( 'initEdit', function () {
        editor.field( 'EditedBy' ).val( username );
    });

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );

    } );

Here's the debug SQL as well:

...
"debug":["Editor PHP libraries - version 2.1.3",
    {"query":"SELECT `Facility_ID` as 'Facility_ID', `Facility_Type` as 'Facility_Type', 
        `Prison_Name` as 'Prison_Name', `Address_1` as 'Address_1', `Address_2` as 'Address_2', 
        `Address_3` as 'Address_3', `City` as 'City', `State` as 'State', `Zipcode` as 'Zipcode', 
        `Mailing_Notes` as 'Mailing_Notes', `Phone_Number` as 'Phone_Number', 
        `Mail_Addr_1` as 'Mail_Addr_1', `Mail_Addr_2` as 'Mail_Addr_2', `Mail_Addr_3` as 'Mail_Addr_3', 
        `Mail_City` as 'Mail_City', `Mail_State` as 'Mail_State', `Mail_Zip` as 'Mail_Zip', 
        `Pkg_Addr_1` as 'Pkg_Addr_1', `Pkg_Addr_2` as 'Pkg_Addr_2', `Pkg_Addr_3` as 'Pkg_Addr_3', 
        `Pkg_City` as 'Pkg_City', `Pkg_State` as 'Pkg_State', `Pkg_Zip` as 'Pkg_Zip', 
        `Alt_Addr_1` as 'Alt_Addr_1', `Alt_Addr_2` as 'Alt_Addr_2', `Alt_Addr_3` as 'Alt_Addr_3', 
        `Alt_City` as 'Alt_City', `Alt_State` as 'Alt_State', `Alt_Zip` as 'Alt_Zip', 
        `Website` as 'Website', `DOC_ACR` as 'DOC_ACR', `eOnly` as 'eOnly', 
        `eProvider` as 'eProvider', `EditedBy` as 'EditedBy', `correspondence` as 'correspondence', 
        `publications` as 'publications', `books` as 'books', `timestamp` as 'timestamp' 
        FROM `PrisonFacilityListing` ","bindings":[]},
{"query":"SELECT DISTINCT `Type` as 'Type', `Type` as 'Type' FROM `FacilityTypes` ","bindings":[]},
{"query":"SELECT `Facility_Type` as 'value', `Facility_Type` as 'label' FROM `PrisonFacilityListing` 
GROUP BY `Facility_Type`","bindings":[]},{"query":"SELECT `City` as 'value', `City` as 'label' 
FROM `PrisonFacilityListing` GROUP BY `City`","bindings":[]},{"query":"SELECT `State` as 'value', `State` as 'label' 
FROM `PrisonFacilityListing` GROUP BY `State`","bindings":[]},{"query":"SELECT DISTINCT `name` as 'name', 
    `name` as 'name' FROM `eProvider` ","bindings":[]}]}.

allan

I see the little icon at the top of the editor screen

Does it stop, or does it stay there forever? Does the Editor window close?

Can you show me the entire response from the server please (from the "raw" view - not the parsed JSON view)?

Allan

Stacey1134

Sure, because of the size, it won't post here, but I will provide a link. The editor window will close if I press the X or Cancel button. Raw response link: (https://drive.google.com/file/d/1kc0QnOceVE_hZCmXI0WAogn2ygTcNtCi/view?usp=sharing "https://drive.google.com/file/d/1kc0QnOceVE_hZCmXI0WAogn2ygTcNtCi/view?usp=sharing")

Stacey1134

Does it stop, or does it stay there forever? Does the Editor window close?

Sorry, yes it's there for about a second and disappears

allan

It might be a copy / paste issue with new lines, but that isn't valid JSON. It has new lines in it. I assume you don't get a "System error" error message when you submit the Editor form?

I'm also concerned that this appears to be the entire data set. Not just the result of an update / edit. Is this the JSON from after submitting an edit? There isn't an UPDATE statement listed in the SQL executed. I suspect this is the data from the initial table load? Can you show me the raw response from an edit action please?

Thanks,
Allan

Stacey1134

So I am not getting an update after submitting an edit. It's whatever your module is throwing back. I will re-write the script after my launch on the 1st. I am not having problems with any other tables using editor, well except for not being able to use the upload plugin with databasename.tablename.table notation. Sometimes I am finding it easer NOT to use editor for complex queries, which this is not, so I am stumped especially since it worked fine for over a month. Thanks for looking into it. Most of the data is in, and good enough for launch. If it still doesn't work after I rewrite it, I'll ask for help again.

P.S. Still need a workaround for getting the upload plugin to work, I really need to use editor for that project.

allan

Okay thanks for letting me know. Let me know how you get on.

Still need a workaround for getting the upload plugin to work, I really need to use editor for that project.

I've got it in a tab to look into in more detail.

Thanks,
Allan

Stacey1134

okay, here's what I got. When the table loads I get this:

 [
   {
      "DT_RowId": "row_1000321",
      "Facility_ID": "1000321",
      "Facility_Type": null,
      "Prison_Name": "Stanislaus County REACT (Re-Entry and Enhanced Alternative to Custody Training) Center",
      "Address_1": null,
      "Address_2": null,
      "Address_3": null,
      "City": null,
      "State": null,
      "Zipcode": null,
      "Mailing_Notes": null,
      "Phone_Number": null,
      "Mail_Addr_1": null,
      "Mail_Addr_2": null,
      "Mail_Addr_3": null,
      "Mail_City": null,
      "Mail_State": null,
      "Mail_Zip": null,
      "Pkg_Addr_1": null,
      "Pkg_Addr_2": null,
      "Pkg_Addr_3": null,
      "Pkg_City": null,
      "Pkg_State": null,
      "Pkg_Zip": null,
      "Alt_Addr_1": null,
      "Alt_Addr_2": null,
      "Alt_Addr_3": null,
      "Alt_City": null,
      "Alt_State": null,
      "Alt_Zip": null,
      "Website": null,
      "DOC_ACR": null,
      "eOnly": "",
      "eProvider": "",
      "EditedBy": "",
      "correspondence": "2",
      "publications": "3",
      "books": "3",
      "timestamp": "2022-11-19 03:33:51"
   },
   {
      "DT_RowId": "row_1006079",
      "Facility_ID": "1006079",
      "Facility_Type": null,
      "Prison_Name": "US Virgin Islands Detention Facility (ICE)",
      "Address_1": null,
      "Address_2": null,
      "Address_3": null,
      "City": null,
      "State": null,
      "Zipcode": null,
      "Mailing_Notes": null,
      "Phone_Number": null,
      "Mail_Addr_1": null,
      "Mail_Addr_2": null,
      "Mail_Addr_3": null,
      "Mail_City": null,
      "Mail_State": null,
      "Mail_Zip": null,
      "Pkg_Addr_1": null,
      "Pkg_Addr_2": null,
      "Pkg_Addr_3": null,
      "Pkg_City": null,
      "Pkg_State": null,
      "Pkg_Zip": null,
      "Alt_Addr_1": null,
      "Alt_Addr_2": null,
      "Alt_Addr_3": null,
      "Alt_City": null,
      "Alt_State": null,
      "Alt_Zip": null,
      "Website": null,
      "DOC_ACR": null,
      "eOnly": "",
      "eProvider": "",
      "EditedBy": "",
      "correspondence": "2",
      "publications": "3",
      "books": "3",
      "timestamp": "2022-11-19 03:33:51"
   },
   {
      "DT_RowId": "row_1006286",
      "Facility_ID": "1006286",
      "Facility_Type": "City Jail",
      "Prison_Name": " Valdez City Jail",
      "Address_1": "206 Pioneer Drive ",
      "Address_2": "",
      "Address_3": null,
      "City": "Valdez",
      "State": "AK",
      "Zipcode": "99686 ",
      "Mailing_Notes": "",
      "Phone_Number": " 907-835-3734",
      "Mail_Addr_1": "206 Pioneer Drive ",
      "Mail_Addr_2": "",
      "Mail_Addr_3": null,
      "Mail_City": "Valdez",
      "Mail_State": "AK",
      "Mail_Zip": "99686 ",
      "Pkg_Addr_1": "206 Pioneer Drive ",
      "Pkg_Addr_2": "",
      "Pkg_Addr_3": null,
      "Pkg_City": "Valdez",
      "Pkg_State": "AK",
      "Pkg_Zip": "99686 ",
      "Alt_Addr_1": null,
      "Alt_Addr_2": null,
      "Alt_Addr_3": null,
      "Alt_City": null,
      "Alt_State": null,
      "Alt_Zip": null,
      "Website": "",
      "DOC_ACR": "ADC",
      "eOnly": "",
      "eProvider": "",
      "EditedBy": "",
      "correspondence": "2",
      "publications": "3",
      "books": "3",
      "timestamp": "2023-06-05 20:18:46"
   },
   {
      "DT_RowId": "row_1006288",
      "Facility_ID": "1006288",
      "Facility_Type": "City Jail",
      "Prison_Name": "Kodiak Jail",
      "Address_1": "2160 Mill Bay Road ",
      "Address_2": "",
      "Address_3": null,
      "City": "Kodiak",
      "State": "AK",
      "Zipcode": "99615",
      "Mailing_Notes": "",
      "Phone_Number": "",
      "Mail_Addr_1": "2160 Mill Bay Road ",
      "Mail_Addr_2": "",
      "Mail_Addr_3": null,
      "Mail_City": "Kodiak",
      "Mail_State": "AK",
      "Mail_Zip": "99615",
      "Pkg_Addr_1": "2160 Mill Bay Road ",
      "Pkg_Addr_2": "",
      "Pkg_Addr_3": null,
      "Pkg_City": "Kodiak",
      "Pkg_State": "AK",
      "Pkg_Zip": "99615",
      "Alt_Addr_1": null,
      "Alt_Addr_2": null,
      "Alt_Addr_3": null,
      "Alt_City": null,
      "Alt_State": null,
      "Alt_Zip": null,
      "Website": "",
      "DOC_ACR": "ADC",
      "eOnly": "",
      "eProvider": "",
      "EditedBy": "",
      "correspondence": "2",
      "publications": "3",
      "books": "3",
      "timestamp": "2023-06-05 20:18:46"
   },
   {
      "DT_RowId": "row_1006290",
      "Facility_ID": "1006290",
      "Facility_Type": "City Jail",
      "Prison_Name": "Craig Jail  ",
      "Address_1": "506 2nd Street ",
      "Address_2": "",
      "Address_3": null,
      "City": "Craig",
      "State": "AK",
      "Zipcode": "99921",
      "Mailing_Notes": "",
      "Phone_Number": "907-826-3300",
      "Mail_Addr_1": "506 2nd Street ",
      "Mail_Addr_2": "",
      "Mail_Addr_3": null,
      "Mail_City": "Craig",
      "Mail_State": "AK",
      "Mail_Zip": "99921",
      "Pkg_Addr_1": "506 2nd Street ",
      "Pkg_Addr_2": "",
      "Pkg_Addr_3": null,
      "Pkg_City": "Craig",
      "Pkg_State": "AK",
      "Pkg_Zip": "99921",
      "Alt_Addr_1": null,
      "Alt_Addr_2": null,
      "Alt_Addr_3": null,
      "Alt_City": null,
      "Alt_State": null,
      "Alt_Zip": null,
      "Website": "",
      "DOC_ACR": "ADC",
      "eOnly": "",
      "eProvider": "",
      "EditedBy": "",
      "correspondence": "2",
      "publications": "3",
      "books": "3",
      "timestamp": "2023-06-05 20:18:46"
   }
]

When I submit an edit, and press the submit button, I get no response at all. Hope that helps!

Stacey1134

Added this

        table.on( 'xhr', function () {
            var json = table.ajax.json();
            console.log( JSON.stringify(json.data, null, 3));
        } );

kthorngren

You won't see a response using the xhr event for the table, which is the Datatable, as the response you are looking for is from the Editor's ajax request. Use the browser's network inspector tool to see the XHR response. See the steps in this technote.

Kevin

Stacey1134

Friggin heck!. Thanks so much @kthorngren !

Showed me where the problem was (BTW was a field failing validation that was collapsed so I didn't see the warning. Geesh!)

Two awesome tools in one Day