DataTables stuck on processing data, json_encode SQL - PHP

Question

When trying to put data from SQL to DataTables using json_encode in PHP, the table just continues to show "Processing..."

My Console Log shows:

Uncaught TypeError: undefined is not a function highcharts.js:17(anonymous function) highcharts.js:17(anonymous function) highcharts.js:308 XHR finished loading: POST "http://website.com/data.php". jquery.js:4 Uncaught TypeError: Cannot read property 'length' of undefined jquery.dataTables.js:2673_fnAjaxUpdateDraw jquery.dataTables.js:2673(anonymous function) jquery.dataTables.js:2519baseAjax.success jquery.dataTables.js:2443m.Callbacks.j jquery.js:2m.Callbacks.k.fireWith jquery.js:2x jquery.js:4m.ajaxTransport.send.b jquery.js:4XMLHttpRequest.send (async)m.ajaxTransport.send jquery.js:4m.extend.ajax jquery.js:4_fnBuildAjax jquery.dataTables.js:2495_fnAjaxUpdate jquery.dataTables.js:2515_fnDraw jquery.dataTables.js:1982_fnReDraw jquery.dataTables.js:2101_fnInitialise jquery.dataTables.js:3276(anonymous function) jquery.dataTables.js:6510m.extend.each jquery.js:2m.fn.m.each jquery.js:2DataTable jquery.dataTables.js:6041(anonymous function) datatables:176m.Callbacks.j jquery.js:2m.Callbacks.k.fireWith jquery.js:2m.extend.ready jquery.js:2J jquery.js:2

My index.php is like this:

<table id="table_id" class="display">
    <thead>
        <tr>
            <th>_RowNumber</th>
            <th>DNumber</th>
            <th>ItemNumber</th>
        </tr>
    </thead>



</table>



<script>
$(document).ready(function() {
    $('#table_id').dataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "<?php echo url(); ?>/new/data.php",
            "type": "POST"
        },
        "columns": [
            { "data": "_RowNumber" },
            {"data": "DNumber"},
            { "data": "ItemNumber" }
        ]
    } );
} );
</script>

and data.php is like this:

<?php
$SQL = "SELECT *
FROM   (SELECT Row_number()
                 OVER (
                   ORDER BY _sys_id ) AS _RowNumber,
               *
        FROM   (SELECT PList.deliverynumber AS DNumber,
                       PLine.picklistnumber,
                       PLine.picklistsuffix,
                       itemnumber,
                       fromlocation,
                       expectedquantity,
                       pickedquantity,
                       currentlocation,
                       ordernumber,
                       orderline,
                       PLine.complete,
                       PLine.insertdate,
                       itemdescription,
                       PList.complete       AS StatusCode,
                       PList.complete       AS Description,
                       PList.id             AS _SYS_ID
                FROM   picklist PList
                       JOIN pickline PLine
                         ON PList.picklistsuffix = PLine.picklistsuffix
                            AND PList.picklistnumber = PLine.picklistnumber) AS
               LIST_TABLE
        WHERE  1 = 1) AS tmp_rows
WHERE  1 = 1
       AND _RowNumber BETWEEN 1 AND 10  
        ";


$Query = $pdo->prepare($SQL);
$Query->execute();
$rows = array();
foreach ($pdo->query($SQL) as $row) {

    $rows[] = $row;


}

print json_encode($rows);
?>

The outcome when navigating to data.php shows like this:

    [{"_RowNumber":"1","0":"1","DNumber":"0800","1":"0800","PickListNumber":"12667","2":"12667","PickListSuffix":"1","3":"1","ItemNumber":"FFT112","4":"FFT112","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12667","9":"12667","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"2","0":"2","DNumber":"0999","1":"0999","PickListNumber":"12667","2":"12667","PickListSuffix":"1","3":"1","ItemNumber":"FFT112","4":"FFT112","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12667","9":"12667","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"3","0":"3","DNumber":"3333","1":"3333","PickListNumber":"12667","2":"12667","PickListSuffix":"1","3":"1","ItemNumber":"FFT112","4":"FFT112","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12667","9":"12667","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"4","0":"4","DNumber":"1221","1":"1221","PickListNumber":"12667","2":"12667","PickListSuffix":"1","3":"1","ItemNumber":"FFT112","4":"FFT112","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12667","9":"12667","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"5","0":"5","DNumber":"6665","1":"6665","PickListNumber":"12667","2":"12667","PickListSuffix":"1","3":"1","ItemNumber":"FFT112","4":"FFT112","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12667","9":"12667","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"6","0":"6","DNumber":"7764","1":"7764","PickListNumber":"55555","2":"55555","PickListSuffix":"1","3":"1","ItemNumber":"FFT412","4":"FFT412","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12637","9":"12637","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"7","0":"7","DNumber":"3322","1":"3322","PickListNumber":"55555","2":"55555","PickListSuffix":"1","3":"1","ItemNumber":"FFT412","4":"FFT412","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12637","9":"12637","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"8","0":"8","DNumber":"1221","1":"1221","PickListNumber":"55555","2":"55555","PickListSuffix":"1","3":"1","ItemNumber":"FFT412","4":"FFT412","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12637","9":"12637","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"9","0":"9","DNumber":"1997","1":"1997","PickListNumber":"55555","2":"55555","PickListSuffix":"1","3":"1","ItemNumber":"FFT412","4":"FFT412","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12637","9":"12637","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"},
{"_RowNumber":"10","0":"10","DNumber":"1696","1":"1696","PickListNumber":"55555","2":"55555","PickListSuffix":"1","3":"1","ItemNumber":"FFT412","4":"FFT412","FromLocation":"LONDON","5":"LONDON","ExpectedQuantity":"1.0","6":"1.0","PickedQuantity":"1.0","7":"1.0","CurrentLocation":"DESPATCH","8":"DESPATCH","OrderNumber":"12637","9":"12637","OrderLine":"1","10":"1","Complete":"1","11":"1","InsertDate":"2014-05-30 16:17:14.000","12":"2014-05-30 16:17:14.000","ItemDescription":"New Today","13":"New Today","StatusCode":"F","14":"F","Description":"F","15":"F","_SYS_ID":"133","16":"133"}]

What do I need to change for the data to display in the DataTables table? https://datatables.net/

Answer 1

If you also use the background page，Maybe you can solve this problem like this： When you return to the data structure of the paging information，You can reset the returned sEcho to 0，like this: eg:

dataSource.setsEcho(0);//I try set 0，It's work for me，you can try
dataSource.setAaData(tempList);
dataSource.setiTotalDisplayRecords(totalNum);
dataSource.setiTotalRecords(totalNum);

Answer 2

Please refer to this link for your answer.http://legacy.datatables.net/usage/server-side just use sAjaxDataProp configuration item to fix it

Answer 3

I found the problem to be I had to use

$rows['aaData'][]

instead of just

$rows[]

In the foreach() statement