drs3925
drs3925
2014-11-21 15:38

DataTables坚持处理数据,json_encode SQL - PHP

已采纳

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/

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • drt3751 drt3751 7年前

    I found the problem to be I had to use

    $rows['aaData'][]
    

    instead of just

    $rows[]
    

    In the foreach() statement

    点赞 评论 复制链接分享
  • dporb84480 dporb84480 7年前

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

    点赞 评论 复制链接分享
  • douliedu335997 douliedu335997 5年前

    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);
    
    点赞 评论 复制链接分享

为你推荐