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/

nihaoqiulinhe · Accepted Answer · 2016-12-07 08:26:46Z

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);

answered Dec 7, 2016 at 8:26

nihaoqiulinhe

2693 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Koala · Accepted Answer · 2014-11-21 16:41:19Z

0

I found the problem to be I had to use

$rows['aaData'][]

instead of just

$rows[]

In the foreach() statement

answered Nov 21, 2014 at 16:41

Koala

5,5634 gold badges27 silver badges34 bronze badges

Comments

Russel - Jiang · Accepted Answer · 2014-12-13 02:49:43Z

0

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

answered Dec 13, 2014 at 2:49

Russel - Jiang

1

1 Comment

Martin Tournoij Over a year ago

You should probably expand on this answer a little bit more (perhaps an example?) to prevent it from being marked as a link-only answer (and be deleted).

Collectives™ on Stack Overflow

DataTables stuck on processing data, json_encode SQL - PHP

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related