Reloading DataTable using JSON Data

MichaelEC

Hi,

I'm trying to reload my datatable with a different query - I'm getting back the correct results when looking at the data in the network tab of developer tools, although the table refuses to essentially re-create.

I've used a big mix of different functions & reloads although nothing seems to re-create the table as intended. For example, when a button is pressed, all data entries with a quantity of 0 are shown - when the button is pressed again, the original entries are shown etc.

Here is my ajax code (how it currently is, is the best I've got it to work so far);

            $('#show_zeros').on('ifChecked', function(event){
                // alert('Show zeroes checked');
                 show_zeros = 'true';

                    $.ajax({
                     url: "table_load.php",
                     data: {'show_zeros' :  show_zeros},
                     type: 'post',
                     success: function(result) {
                            table25.destroy();
                         $('#tbl_products_list').html(result);
                     },
                         error: function() {}

                     });
                });

            $('#show_zeros').on('ifUnchecked', function(event){
                    //alert('Show zeroes unchecked');
                    show_zeros = 'false';

                     $.ajax({
                     url: "table_load.php",
                     data: {'show_zeros' :  show_zeros},
                     type: 'post',
                     success: function(result) {
                            table25.destroy();
                         $('#tbl_products_list').html(result);
                     },
                         error: function() {}

                     });
                });

Here is my datatables code;

            var table25 = $('#tbl_products_list').DataTable( {
                "ajax": "./table_load.php",
                "deferRender": true,
                fixedColumns:   {
                            leftColumns: 1
                    },
                    "language": {
     "loadingRecords": "Loading products (If any!)"
},
                    "order": [],
                "aoColumnDefs": [
                    { "sClass": "columnTestClass", "aTargets": [ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ] },
                    {
                        "targets" : [ 3, 4, 5 ],
                        "visible" : false,
                    }
                    ],
                "dataSrc": "tableData",
                scrollY:        "685px",
                        scrollX:        true,
                        scrollCollapse: true,
                 "dom": '<"top_display_llll"B>rtip',
                  stateSave: true,
                 //  pageLength: 25,
                  colReorder: true,
                 colReorder: {
             fixedColumnsLeft: 1
      },
               // "iDisplayLength": 25,
                 buttons: [
                     {
                     extend: 'colvis',
                     className: 'lolbutton',
                     columns: ':eq(1),:eq(2),:eq(3),:eq(4),:eq(5),:eq(6),:eq(7),:eq(8),:eq(9), :eq(10), :eq(11)'
             }
            ],
                 });

                 window.onbeforeunload = function(){
                        table25.state.clear();
                };


                $('a[data-toggle="tab"]').on( 'shown.bs.tab', function (e) {
        $($.fn.dataTable.tables( true ) ).css('width', '100%');
        $($.fn.dataTable.tables( true ) ).DataTable().columns.adjust().draw();
    } );

                 $('#mySearchProd').on( 'keyup', function () {
                    table25.search( this.value ).draw();
            } );

The entirety of the data comes from a file named table_load.php - here is the data array if it helps:

  $dataarray['data'][] = array(
                   $row['product_name'], $row['department_name'],
                   $prev_quantity_units, $purchases_quantity,
                   $transfer_quantity, $theoretical_quantity,
                   number_format($row['quantity_counted'],2),
                   "<div style='color: $color;'>$difference_quantity</div>",
                   "<div style='color: $color_zero;'>".number_format($row['unit_cost'],2),
                   $prev_quantity_amount,
                   number_format($row['total_cost'],2),
                  "<div style='color: $color;'>$difference_cost</div>",
                   "<button style='margin: 0 auto; float: none;'  id='products_button' class='btn dt_buttons ajax_forms' data-keyid='' data-source='get_product_data.php' data-id='".$row['product_id']."' data-target='popup' data-element=''><i class='fa fa-list fa-md'></i><span>&nbsp; Edit </span></button>", "<span style='height: 100%; width: 100%;' class='delete_record_prod' id='ProductMaster_".string_encrypt($row['product_id'])."'> <i class='fa fa-trash-o fa-lg'></i> </span>"
                 );
    }
    //print_r($dataarray);
$jsonData = json_encode($dataarray);
print_r($jsonData);

I know this seems to be a rather large question! Although I just can't wrap my head around it, any help is appreciated.

kthorngren

In the ajax success functions your are destroying the Datatable but never reinitializing it. Looks like you are calling the same Ajax URL in the Datatables ajax and in the jQuery ajax calls. Presumably the format of the data coming back is the same for each??

Instead of using:

                table25.destroy();
             $('#tbl_products_list').html(result);

In your ajax calls I would try the following, assuming the data format is the same as the original Datatables ajax request:

table25.clear();
table25.rows.add(result).draw();

Using result in add(result) might not be correct and you will need to change what you supply to rows.add(). If you need help with this please post a snippet of the response.

Kevin

MichaelEC

Hi Kthorngren,

Thanks a lot for that, I assume I will certainly have to change rows.add() a bit as I'm getting a weird result returned! If it helps here's what I'm getting back from the html(result):
https://gyazo.com/db13d71e66eb08df13e82678a3105e54

And here's what the table is now populating:
https://gyazo.com/11dc4562115ac8a390575918cd2a00d3

And here's the error message:
"DataTables warning: table id=tbl_products_list - Requested unknown parameter '1' for row 0, column 1"

kthorngren

Datatables Ajax defaults to using the data object in the response. Rows.add() and other methods to add data to Datatbles don't. Look slike you need to use table25.rows.add( result.data ).draw();

Kevin

MichaelEC

Getting better it seems!

Just another error, one that looks a bit more usual;

"Cannot read property 'length' of undefined"

Is there an issue with reading my data?

kthorngren

You may need to parse the JSON. Try this:

result = JSON.parse(result);
table25.clear();
table25.rows.add(result).draw();

Kevin

MichaelEC

Yet again you've got it Kthorngren, thanks a lot!

If I understand correctly, my mistake was not understanding how to handle the parsing & refreshing of JSON data into DataTables? if I'm not using JSON it seems to be straight forward!