php - DataTables - 使用搜索栏时出现无效的 JSON 消息

Question

我正在尝试实现 this使用 DataTables 并且数据正确显示时，每当我尝试使用搜索栏、过滤器时，我都会收到以下错误:

DataTables 警告:表 id= checkin - JSON 响应无效。有关此错误的更多信息，请参阅http://datatables.net/tn/1

当我点击该链接并按照说明操作时，这是来自 response.php 的 json:

{"draw":1,"recordsTotal":3,"recordsFiltered":3,"data":[["28","708089113","Nicole Foster","","","2016-04-15 14:27:36"],["29","708089113","Larry Quaglia","","","2016-04-15 14:28:38"],["30","708089113","Nicole Foster","Test","test@syr.edu","2016-04-21 09:18:59"]]}

我对 JSON 和 AJAX 还很陌生，但是当我检查该页面上的 JSON Lint 和 JSON Parser 链接输出的内容时，它说它们是有效的。

这是我的代码:

Reports.php(仅显示调用 DataTables 的脚本)

<script>
    $( document ).ready(function() {
        $('#checkin').DataTable({
            "bProcessing": true,
            "serverSide": true,
            "dom": 'lBfrtip',
            "ajax":{
                url :"response.php", // json datasource
                type: "post"  // type of method  ,GET/POST/DELETE
                },
            "buttons":[
                {
                    extend: 'collection',
                    text: 'Export',
                    buttons: [
                            'copy',
                            'excel',
                            'csv',
                            'pdf',
                            'print'
                    ]
                }
            ]
            });   
    });
</script>

响应.php

<?php
//include connection file 
include_once("../connection.php");

// initilize all variable
$params = $columns = $totalRecords = $data = array();

$params = $_REQUEST;

//define index of column
$columns = array( 
    0 => 'id',
    1 => 'suid', 
    2 => 'staffMember',
    3 => 'studentName',
    4 => 'studentEmail',
    5 => 'checkinDateTime'
);

$where = $sqlTot = $sqlRec = "";

// check search value exist
if( !empty($params['search']['value']) ) {   
    $where .=" WHERE ";
    $where .=" ( studentName LIKE '".$params['search']['value']."%' ";    
    $where .=" OR staffMember LIKE '".$params['search']['value']."%' ";
    $where .=" OR studentEmail LIKE '".$params['search']['value']."%' ";
    $where .=" OR suid LIKE '".$params['search']['value']."%' ";
    $where .=" OR checkinDate LIKE '".$params['search']['value']."%' )";
}

// getting total number records without any search
$sql = "SELECT * FROM `checkin` ";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {

    $sqlTot .= $where;
    $sqlRec .= $where;
}


$sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";

$queryTot = mysqli_query($VisitorManagement, $sqlTot) or die("database error:". mysqli_error($VisitorManagement));


$totalRecords = mysqli_num_rows($queryTot);

$queryRecords = mysqli_query($VisitorManagement, $sqlRec) or die("error to fetch check-in data");

//iterate on results row and create new index array of data
while( $row = mysqli_fetch_row($queryRecords) ) { 
    $data[] = $row;
}   

$json_data = array(
        "draw"            => intval( $params['draw'] ),   
        "recordsTotal"    => intval( $totalRecords ),  
        "recordsFiltered" => intval($totalRecords),
        "data"            => $data   // total data array
        );

echo json_encode($json_data);  // send data as json format
?>

连接.php

$hostname_VisitorManagement = "localhost";
$database_VisitorManagement = "visitor-management";
$username_VisitorManagement = "***";
$password_VisitorManagement = "***";
$VisitorManagement = mysqli_connect($hostname_VisitorManagement, $username_VisitorManagement, $password_VisitorManagement, $database_VisitorManagement);

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

date_default_timezone_set('America/New_York');

我有什么遗漏的吗？我在他们的论坛上浏览过，但我还没有看到使用像我这样的代码来解决与我类似的问题的解决方案。

Answer 1

尝试将选项serverSide更改为false。如果设置为 true，则每次您尝试过滤/排序/更改页面时，表都会查询 ajax 源。如果您打算先检索所有数据，然后在客户端工作，则 serverSide 选项应为 false

更新:所以我猜测问题是数据表在进行 ajax 调用时没有发送任何参数，并且查询构建不正确。

尝试将此类参数添加到 ajax.data 选项中:

"ajax":{
     url :"response.php", // json datasource
     type: "post",  // type of method  ,GET/POST/DELETE
     data: {}//data to send
 },