Row not updating after editing data

kumamari

I have an issue with my rows data not updating after I edit the data in my editor. I can see the data has successfully been edited in my database, but it's not reflecting on the datatable.

Here is my code.

table = $('#myTable').DataTable({
        dom: 'ZBfrtip',
        scrollX: true,
        scrollY: 615,
        processing: true,
        serverSide: true,
        ajax: {
            url: "/itemCreation/getRecords.php"
        },
        columns: [
            { data: "button" },
            { data: "item_id" },
            { data: "item_desc" },
            { data: "item_name" },
            { data: "item_detail" },
            { data: "bid_start" },
            { data: "min_bid_increment" },
            { data: "bid_increment" },
            { data: "fair_market" },
            { data: "group_id" },
            { data: "categories" },
            { data: "confirm_bids" },
            { data: "consign" },
            { data: "buy_out" },
            { data: "auc_start" },
            { data: "start_time" },
            { data: "tzoffset" },
            { data: "taxable" },
            { data: "display_item" }
        ],
        columnDefs: [
            { width: '20%', targets: 0 }
        ],
        colResize: {
            tableWidthFixed: false,
            handleWidth: 10
        },
        fixedColumns: true,
        searching: true
});

  
    setInterval(function() {
        table.ajax.reload(null,false);
    }, 3000);

What am I missing?

kthorngren

Are you using the Datatables Editor? Its not shown in your code.

If you are... Are you returning the edited data to the client as described in this doc?
https://editor.datatables.net/manual/server#Example-data-exchanges

If you reload that page does it show the correct data?

Kevin

kumamari

Yes, I am using the Datatables Editor.

I am now returning this data via my php script through an ajax call.

{
    "data": [{
        "DT_RowId": "257",
        "item_id": "001",
        "auc_id": 1,
        "item_detail": "",
        "item_name": "",
        "0": "item_desc",
        "1": "lorem ipsumx",
        "categories": "",
        "fair_market": "0.00",
        "confirmBids": "0.00",
        "consign": "0.00",
        "bid_start": "0.00",
        "min_bid_increment": "0.00",
        "tzoffset": "",
        "auc_start": "1899-11-30 ::00",
        "auc_end": "2099-12-23 00:00:00",
        "start_time": "",
        "display_item": "",
        "buy_out": "0.00",
        "taxable": "0",
        "group_id": ""
    }]
}

I then do

table.ajax.reload still nothing changes. Weird thing is when I create my items, the row gets added on the table.

kumamari

Here is all my code for reference. I can't seem to find a solution.

editor = new $.fn.dataTable.Editor( {
    ajax:  '/itemCreation/getRecords.php',
    table: '#scratchTable',
    display: 'lightbox',
    idSrc: "id",
    fields: [ {
        label: "Item ID:",
        name: "item_id"
    }, {
        label: "Short Description:",
        name: "item_desc"
    }, {
        label: "Full Description:",
        name: "item_name"
    }, {
        label: "Full Detail:",
        name: "item_detail"
    }, {
        label: "Starting Bid:",
        name: "bid_start"
    }, {
        label: "Minimum Bid Increment:",
        name: "min_bid_increment"
    }, {
        label: "Bid Increment:",
        name: "bid_increment"
    }, {
        label: "Fair Market Value:",
        name: "fair_market"
    }, {
        label: "Group ID:",
        name: "group_id"
    }, {
        label: "Category:",
        name: "categories"
    }, {
        label: "Confirm Bids Over:",
        name: "confirm_bids"
    }, {
        label: "Consignment Amount:",
        name: "consign"
    }, {
        label: "Buy Out Amount:",
        name: "buy_out"
    }, {
        label: "Start Date:",
        name: "auc_start",
        type: 'datetime'
    }, {
        type:  "select",
        label: "Time Zone:",
        name:  "tzoffset",
        options: [
            { label : "Please select", value: '' },
            { label : "Eastern", value: '0' },
            { label : "Central", value: '1' },
            { label : "Mountain", value: '2' },
            { label : "Pacific", value: '3' },
            { label : "Alaska", value: '4' },
            { label : "Hawaii", value: '5' }
        ]
    }, {
        label: "Start Time:",
        name: "start_time",
        type: "select",
        options: [
            { label : "Please select", value: '' },
            "11:45 P.M."
        ]
    },  {
        type:  "select",
        label: "Display:",
        name:  "display_item",
        options: [
            { label : "Please select", value: '' },
            { label : "ON", value: '1' },
            { label : "OFF", value: '0' }
        ]
    }, {
        type:  "select",
        label: "Taxable:",
        name:  "taxable",
        options: [
            { label : "Please select", value: '' },
            { label : "ON", value: '1' },
            { label : "OFF", value: '0' }
        ]
    }, {
        label: "Primary Image:",
        name: "primary",
        type: "upload",
        display: function (file_id) {
            console.log(file_id);
            var d = new Date();
            return '<img class="imageCollect" src="https://photo.qtego.net/images/' + org_id + '/temp/' + file_id + '_scratch_primary.jpg?timestamp=' + d.getTime() + '" />';
        },
        clearText: "Remove Image",
        noFileText: 'No image'
    }, {
        label: "Secondary Images:",
        name: "secondary",
        type: "uploadMany",
        display: function (file_id) {
            var d = new Date();
            return '<div class="secondaryContainer"><img class="imageCollect" src="https://photo.qtego.net/images/' +org_id +  '/temp/' + file_id + '_scratch_secondary.jpg?timestamp=' + d.getTime() + '" /></div>';
        },
        clearText: "Remove Image",
        noFileText: 'No image'
    }]
} );

var table = $("#scratchTable").DataTable({
    dom: 'ZBfrtip',
    scrollX: true,
    scrollY: 615,
    processing: true,
    serverSide: true,
    ajax: {
        url: "/itemCreation/getRecords.php"
    },
    columns: [
        { data: "button" },
        { data: "item_id" },
        { data: "item_desc" },
        { data: "item_name" },
        { data: "item_detail" },
        { data: "bid_start" },
        { data: "min_bid_increment" },
        { data: "bid_increment" },
        { data: "fair_market" },
        { data: "group_id" },
        { data: "categories" },
        { data: "confirm_bids" },
        { data: "consign" },
        { data: "buy_out" },
        { data: "auc_start" },
        { data: "start_time" },
        { data: "tzoffset" },
        { data: "taxable" },
        { data: "display_item" }
    ],
    columnDefs: [
        { width: '20%', targets: 0 }
    ],
    colResize: {
        tableWidthFixed: false,
        handleWidth: 10
    },
    fixedColumns: true,
    searching: true,
    language: {
        "emptyTable": "No items have been created."
    },
    lengthMenu: [[20, 25, 50, -1], [10, 25, 50, "All"]],
    select: true,
    buttons: [
        {
            extend: 'create',
            editor: editor,
            formButtons: [
                {
                    label: 'Cancel',
                    fn: function () {
                        this.close();
                    }
                },
                {
                    label: 'Create',
                    fn: function () {
                        var itemSubmit = '/itemCreation/saveItem.php';
                        var itemIDPre = editor.val('item_id');
                        var itemID = itemIDPre;
                        var shortDescription = editor.val('item_desc');
                        var fullDescription = editor.val('item_name');
                        var fullDetail = editor.val('item_detail');
                        var startingBid = editor.val('bid_start');
                        var minimumBid = editor.val('min_bid_increment');
                        var bidIncrement = editor.val('bid_increment');
                        var fairMarket = editor.val('fair_market');
                        var groupID = editor.val('group_id');
                        var category = editor.val('categories');
                        var confirmBids = editor.val('confirm_bids');
                        var consignAmount = editor.val('consign');
                        var buyOut = editor.val('buy_out');
                        var startDate = editor.val('auc_start');
                        var itemStart = editor.val('start_time');
                        var timezone = editor.val('tzoffset');
                        var displayItem = editor.val('display_item');
                        var taxable = editor.val('taxable');
                        var startTime = itemStart;
                        var check;

                        // form values
                        $.ajax({
                            url: itemSubmit,
                            data: {
                                itemID: itemID,
                                shortDescription: shortDescription,
                                fullDescription: fullDescription,
                                fullDetail: fullDetail,
                                startingBid: startingBid,
                                minimumBid: minimumBid,
                                bidIncrement: bidIncrement,
                                fairMarket: fairMarket,
                                groupID: groupID,
                                category: category,
                                confirmBids: confirmBids,
                                consignAmount: consignAmount,
                                buyOut: buyOut,
                                startTime: startTime,
                                itemStart: itemStart,
                                timezone: timezone,
                                displayItem: displayItem,
                                taxable: taxable
                            },
                            type: 'POST'
                        }).done(function(data) {
                            console.log(data);
                        }).fail(function(data) {
                            console.log('fail');
                        });

                        table.draw();
                        this.close();
                    }
                }
            ]
        },
        {
            extend: 'edit',
            editor: editor,
            formButtons: [
                {
                    label: 'Cancel',
                    fn: function () {
                        this.close();
                    }
                },
                {
                    label: 'Update',
                    fn: function () {
                        var itemSubmit = '/itemCreation/saveItem.php';
                        var itemID = editor.val('item_id');
                        var shortDescription = editor.val('item_desc');
                        var fullDescription = editor.val('item_name');
                        var fullDetail = editor.val('item_detail');
                        var startingBid = editor.val('bid_start');
                        var minimumBid = editor.val('min_bid_increment');
                        var bidIncrement = editor.val('bid_increment');
                        var fairMarket = editor.val('fair_market');
                        var groupID = editor.val('group_id');
                        var category = editor.val('categories');
                        var confirmBids = editor.val('confirm_bids');
                        var consignAmount = editor.val('consign');
                        var buyOut = editor.val('buy_out');
                        var startDate = editor.val('auc_start');
                        var itemStart = editor.val('start_time');
                        var timezone = editor.val('tzoffset');
                        var displayItem = editor.val('display_item');
                        var taxable = editor.val('taxable')
                        var startTime = itemStart;
                        var check;

                        if (startDate != '') {
                            itemStart = startDate + ' ' + timeStringOne + ':' + timeStringTwo + ':00';
                        }

                        if (itemStart == 'Please select') {
                            itemStart = '';
                        }

                        function checkItem() {
                            if (editor.val('item_id') == '') {
                                editor.error('item_id','An item id is required.');
                                return false;
                            } else {
                                clearInterval(check);
                                editor.error('item_id','');
                            }
                        }

                        if (itemID === '') {
                            this.error('item_id','An item id is required.');
                            check = setInterval(checkItem,2000);
                            return false;
                        }


                        // form values
                        $.ajax({
                            url: itemSubmit,
                            data: {
                                action: 'edit',
                                itemID: itemID,
                                shortDescription: shortDescription,
                                fullDescription: fullDescription,
                                fullDetail: fullDetail,
                                startingBid: startingBid,
                                minimumBid: minimumBid,
                                bidIncrement: bidIncrement,
                                fairMarket: fairMarket,
                                groupID: groupID,
                                category: category,
                                confirmBids: confirmBids,
                                consignAmount: consignAmount,
                                buyOut: buyOut,
                                startTime: startTime,
                                itemStart: itemStart,
                                timezone: timezone,
                                displayItem: displayItem,
                                taxable: taxable
                            },
                            type: 'POST'
                        }).done(function(data) {
                            console.log(data);
                        }).fail(function(data) {
                            console.log('fail');
                        });

                        this.close();
                        table.ajax.reload();
                    }
                }
            ]
        },
        { extend: 'remove', editor: editor }
    ]
});

allan

Are you using the PHP libraries for Editor? If so, can you also show that code please?

I would suggest removing line 321 (table.ajax.reload();) above. It isn't needed - the table should automatically be updated from the edit result. I realise that is probably an attempted workaround, but if you could remove it for now, as it might be confusing things.

Allan

kumamari

No, I'm not using the libraries atm. I did my own script to echo out my array with json_encode.

I'm getting this back through my network tab on saveItem.php when I edit my entry. I've modified my rows on my html markup to all have an id through the DT_RowId property.

{"data":[{"DT_RowId":"row-257","item_id":"001","auc_id":"1","item_detail":"hey yous","item_name":"","item_desc":"lorem ipsumsss","categories":"","fair_market":"0.00","confirmBids":"0.00","consign":"0.00","bid_start":"0.00","min_bid_increment":"0.00","tzoffset":"","auc_start":"1899-11-30 ::00","auc_end":"2099-12-23 00:00:00","start_time":"","display_item":"","buy_out":"0.00","taxable":"0","group_id":""}]}

I've looked at your samples and it seems this is the exactly the same format. I'm confused as to why this won't edit when I submit my edited entry through the lightbox on the editor.

allan

I'm getting this back through my network tab on saveItem.php

Ah - but your Editor ajax option is:

ajax: '/itemCreation/getRecords.php',

It isn't pointing at the save script.

I think I see the issue now. You aren't calling submit() which trigger's Editor's own save call - thus, you appear to be using your own Ajax call as a workaround for that. The upshot is that Editor can't execute its post submit actions - including updating the row!

Because that is bypassing Editor - if you want to continue using that method, you'd need to also update the row yourself.

Allan

kumamari

Alright, I modified the script to now use the getRecords.php option when it edits. I am doing a call with the submit method on the api. It is now calling a POST action with an upload parameter of edit which I recognize and then go into my array and capture the data. I have it coming back with this data which is inline with what my db has.

{"data":[{"DT_RowId":"row-257","item_id":"001","auc_id":"1","item_detail":"","item_name":"","item_desc":"lorem ipsum","categories":"","fair_market":"0.00","confirmBids":"","consign":"0.00","bid_start":"0.00","min_bid_increment":"0.00","tzoffset":"","auc_start":"","auc_end":"2099-12-23 00:00:00","start_time":"","display_item":"","buy_out":"0.00","taxable":"0","group_id":""}]}

After that is done, it does another call to the getRecords.php with the following object coming back in the network tab.

{"draw":"1","recordsTotal":"1","recordsFiltered":"1","data":[{"button":"<button>Test<\/button>","item_id":"001","item_desc":"lorem ipsum","item_name":"","item_detail":"","bid_start":"0.00","min_bid_increment":"0.00","bid_increment":"0.00","fair_market":"0.00","group_id":"","categories":"","confirm_bids":"0.00","consign":"0.00","buy_out":"0.00","auc_start":"","tzoffset":"Eastern","start_time":"","display_item":"OFF","DT_RowId":"257","primaryID":"109001","secondaryID":"109001","taxable":"0"}]}

I assume at this point, it paints the table, but I am not seeing the table change even though the record gets updated.

Anything jump out?

kthorngren

Sounds like you are emulating the server writing to the DB and responding with pre-canned data.

I see a couple things:

1. The first response doesn't have the buttons column
2. Your columns are defined with the column confirm_bids but the first response has confirmBids

This example shows the Editor sending the edited information and receiving the first response. Then when the table is draw it sends a SSP request for the page data and receives the second response. Same as the behavior you are seeing.

The problem is your second response does not have the changes. I assume this is also pre-canned data to initially populate the data. On the surface it looks like everything is behaving as expected but your server side test code needs a bit of changing to reflect your changes.

Assuming I'm correct about your test code then a slight change might help. Not sure what you are using but maybe you can create a global variable containing the original data object and that will be returned when Datatables initializes. Update the row with the editor then in your code update the global variable object with the new data, return the first response with updated fields and the second response should return with the updated variable. And you should see the table updated. Hope this makes sense.

Kevin

kumamari

I figured the issue out. Here was the primary issue in all this that I didn't realize.

When I would draw the records initially for the datatable I had this format.

{"draw":"1","recordsTotal":"1","recordsFiltered":"1","data":[{"button":"<button>Test<\/button>","item_id":"001","item_desc":"lorem ipsum","item_name":"","item_detail":"","bid_start":"0.00","min_bid_increment":"0.00","bid_increment":"0.00","fair_market":"0.00","group_id":"","categories":"","confirm_bids":"0.00","consign":"0.00","buy_out":"0.00","auc_start":"","tzoffset":"Eastern","start_time":"","display_item":"OFF","DT_RowId":"257","primaryID":"109001","secondaryID":"109001","taxable":"0"}]}

The problem is when drawing the records initially and drawing them on edit, they need the same format. I didn't need the draw, recordsTotal and recordsFiltered keys. What I did need was this format on the initial loading and on the edit. Once I did this, the editing on the records worked. Here is what works. It's the same on the documentation on the site. Also I have the DT_RowId to identify each row. So basically on initial drawing and edit I have the same format of the json encode from the SSP but the values are different because of editing. Hope this helps someone.

{"data":[{"button":"<button>Test<\/button>","DT_RowId":"row_257","item_id":"001","item_desc":"lorem ipsumsxxxxxx","item_name":"boom","item_detail":"boom","group_id":"","auc_id":1,"categories":"","fair_market":"0.00","confirm_bids":"","consign":"0.00","bid_start":"0.00","min_bid_increment":"0.00","tzoffset":"","auc_start":"","auc_end":"2099-12-23 00:00:00","start_time":"","display_item":"","buy_out":"0.00","taxable":"0"}]}