Data is not displayed in table after json call

martin1223345

I came in to a situation that when no data was fetched the table stated uncaught error lengt 0. So i searched for a solution and
this should be added to fix that problem.

 "ajax": {
             url: "./selectdatatable_otheracc.php",
            "dataSrc": ""
        },

The problem with the loading is gone but when there is data it is not showing and says No data available in table.
When i check my console and the network tab the data is fetched, so why is it not showing?

Part of the script

   <script type="text/javascript">
                var checkCol = 12;         //checkbox column
                var php_va = "<?php echo $targetlocation; ?>";
                $(document).ready(function() {
    
                var table = $('#accountTable').dataTable({
                
                    
                "footerCallback": function ( row, data, start, end, display ) {
                var api = this.api(), data;
 
                // converting to interger to find total
                var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // computing column Total of the complete result 
                
                var bulletTotal = api
                .column( 3,{page:'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
                
                var leadTotal = api
                .column( 7, {page:'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
                
                var cashTotal = api
                .column( 8, {page:'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
    
            // Update footer by showing the total with the reference of the column index 
        $( api.column( 0 ).footer() ).html('Total');
           var numberRenderer = $.fn.dataTable.render.number('.', '.', 0).display;
            $( api.column( 3 ).footer() ).html(numberRenderer( bulletTotal ));
            $( api.column( 7 ).footer() ).html(numberRenderer( leadTotal ));
            $( api.column( 8 ).footer() ).html(numberRenderer( cashTotal ));
  
        },
                
                
                "pageLength": 100,
                "lengthMenu": [[50, 100, 200, -1], [50, 100, 200, "All"]],
                "sFilterInput": "form-control search-box",
                "order": [],
                language: { search: '', searchPlaceholder: "Filter table..." },
                rowId: 'id',
                "processing": true,
                
            
                rowCallback: function ( row, data ) {
                if (moment(data.update_script, 'YYYY-MM-DD HH:mm:ss') < moment() && data.category == 6) {
                $(row).css('background-color', ' rgba(255, 0, 0, 0.2)');
                } else {
                $(row).css('background-color', '');
                }   
                var payuntill = moment(data.testpay, 'YYYY-MM-DD HH:mm:ss');
                var dnow = moment();
                if (payuntill.diff(dnow, 'days') <= 0) {
                $('td:eq(0)', row).css('background-color', ' rgba(255, 0, 0, 0.7)');
                }   
  
                if (payuntill.diff(dnow, 'days') > 0 && payuntill.diff(dnow, 'days') < 22) {
                $('td:eq(0)', row).css('background-color', ' rgba(255, 153, 0, 0.7)');
                }
  
                },
                  "ajax": {
             url: "./selectdatatable_otheracc.php",
            "dataSrc": ""
        },
       
                "columns": [
                
                { data:  null,
                title : '!!',
                render: function ( data, type, row ) {
                var testpay = moment(data.testpay, 'YYYY-MM-DD HH:mm:ss');
                var now = moment();
                if ( testpay.diff(now, 'days')<= 0)
                return '<font color='+row.cat_color+'>0</font>';
                else{ return '<font color='+row.cat_color+'>'+ testpay.diff(now, 'days') + '</font>'; 
                } }
                },
                {data: 'name',
                render: function ( data, type, row, meta ) {
                return '<font color='+row.cat_color+'>'+data+'</font>';
                } } , 

martin1223345

The error log without this change..

Uncaught TypeError: Cannot read property 'length' of null
at jquery.dataTables.js:4722
at callback (jquery.dataTables.js:3849)
at Object.success (jquery.dataTables.js:3879)
at i (jquery.min.js:2)
at Object.fireWith [as resolveWith] (jquery.min.js:2)
at z (jquery.min.js:4)
at XMLHttpRequest.<anonymous> (jquery.min.js:4)

kthorngren

Are you saying you added ajax.dataSrc? If so that won't necessarily fix the problem. This option is used to define where the data can be found in the JSON response, if its not in the data object. See the ajax docs for more info. Please post the first part of your JSON response so we can see the structure.

The problem could be somewhere else. Make sure you have a matching number of columns in your HTML table as you defined with columns.

Can you post a link to your page or a test case replicating the issue so we can help debug?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

martin1223345

Thank you. Well ill explain the situation. The sql query to echo the Json is executed by filters. So when one clicks the own accounts menu it could hold 100 accounts, but clicking the on a other account menu it could hold 0 accounts. And that is where this error takes place. When there are no results to display.

The following is shown in my network log:

echo: 0, totalrecords: 0, totaldisplayrecords: 0, data: null}
data: null
echo: 0
totaldisplayrecords: 0
totalrecords: 0

And it is done with the following json / sql output:

```
$dataset = array(

"echo" => 0,
"totalrecords" => count($array),
"totaldisplayrecords" => count($array),
"data" => $array

);

echo json_encode($dataset);

<?php > ``` ?>

So in other words. What do i need to add or change to make sure the table stops showing errors when no results are fetched by the sql?

kthorngren

data: null

Looks like this is the null the error is referring to. Datatables expects an array of data. If there is no data then return an empty array: []

Kevin

martin1223345

Dumb question i guess.. I am searching on google but i cant find how to do that. How do i create that emtpy array?

kthorngren

Thats a good question. I don’t use PHP but the SO thread might help.

Kevin

martin1223345

Found the solution.

$array   = $array ?? [];
$dataset = array(
    "echo"                => 1,
    "totalrecords"        => count($array),
    "totaldisplayrecords" => count($array),
    "data"                => $array,
);