DataTables问题

Question

我试图在DataTables服务器端使用以下代码：

这是Javascript：

<script>

        $(document).ready( function () {

            $('#table').DataTable({
                "serverSide": true,
                "processing": true,
                "ajax": "{{ $url }}",

                "aoColumns": [
                    { "data": null },
                    { "data": "name" },
                    { "data": "playercount" },
                    { "data": "warswon" },
                    { "data": "warslost" },
                    { "data": "warstied" },
                    { "data": "level"},
                    { "data": "exp"},
                    { "data": "location"},
                    { "data": "warwinpercent"}
                ],

                "fnRowCallback": function( nRow, aData, iDisplayIndex ) {
                    $('td:eq(1)', nRow).html('<a href="/clans/' + aData[1] + '">' +
                            aData[3] + '</a>');
                    return nRow;
                },

                "fnRowCallback": function( nRow, aData, iDisplayIndex ) {
                    var index = iDisplayIndex + 1;
                    $('td:eq(0)', nRow).html(index);
                    return nRow;
                }
            });

            $('#table').dataTable().columnFilter({
                sPlaceHolder: "head:before",
                aoColumns: [null, { type: "text" }, null, null, null, null, null, null, { type: "text" }, null]
            });

        } );
    </script>

我的用于服务器处理的PHP代码可以在这里获得：http://pastebin.com/Wpn9u64U

您可以在以下网站看到我的活示例：http://clashdata.tk/clans/

问题是：

• 它只给我看了10张唱片。
• 分页不管用。
• 搜索过滤不起作用。
• 按命令不起作用。

这是怎么回事，为什么它们不能正常工作呢？

我一直在到处找，但什么也找不到。有人说要将serverSide设置为false，但重点是使用serverSide，这样我就不必在页面加载时大量加载数据。

下面是文章中的serverCode：

<?php

namespace App\Helpers\DataTables;

use PDO;
use PDOException;

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);


class DataTables {

    private $_db;

    public function __construct() {

        try {
            $host               = 'localhost';
            $database           = 'zzzzzzzzzzzz';
            $user               = 'xxxxxxxxxxxx';
            $passwd             = 'yyyyyyyyyyyyy';

            $this->_db = new PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
        } catch (PDOException $e) {
            error_log("Failed to connect to database: ".$e->getMessage());
        }

    }

    public function utf8ize($d) {
        if (is_array($d)) {
            foreach ($d as $k => $v) {
                $d[$k] = $this->utf8ize($v);
            }
        } else if (is_string ($d)) {
            return utf8_encode($d);
        }
        return $d;
    }

    public function get($table, $index_column, $columns) {

        // Paging
        $sLimit = "";
        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
            $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
        }

        // Ordering
        $sOrder = "";
        if ( isset( $_GET['iSortCol_0'] ) ) {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                    $sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
                    $sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
                }
            }

            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" ) {
                $sOrder = "";
            }
        }

        /*
         * Filtering
         * NOTE this does not match the built-in DataTables filtering which does it
         * word by word on any field. It's possible to do here, but concerned about efficiency
         * on very large tables, and MySQL's regex functionality is very limited
         */
        $sWhere = "";
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
            $sWhere = "WHERE (";
            for ( $i=0 ; $i<count($columns) ; $i++ ) {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
                    $sWhere .= "`".$columns[$i]."` LIKE :search OR ";
                }
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }

        // Individual column filtering
        for ( $i=0 ; $i<count($columns) ; $i++ ) {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                if ( $sWhere == "" ) {
                    $sWhere = "WHERE ";
                }
                else {
                    $sWhere .= " AND ";
                }
                $sWhere .= "`".$columns[$i]."` LIKE :search".$i." ";
            }
        }

        // SQL queries get data to display

        $stmt = $this->_db->prepare('SET @rownum = 0');
        $stmt->execute();

        $sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."`, @rownum := @rownum+1 AS rank FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
        $statement = $this->_db->prepare($sQuery);

        // Bind parameters
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
            $statement->bindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
        }
        for ( $i=0 ; $i<count($columns) ; $i++ ) {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                $statement->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
            }
        }

        $statement->execute();
        $rResult = $statement->fetchAll();

        $iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());

        // Get total number of rows in table
        $sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
        $iTotal = current($this->_db->query($sQuery)->fetch());

        // Output
        $output = array(
            "draw" => 1,
            "recordsTotal" => $iTotal,
            "recordsFiltered" => $iFilteredTotal,
            "data" => array()
        );

        // Return array of values
        foreach($rResult as $aRow) {
            $row = array();
            for ( $i = 0; $i < count($columns); $i++ ) {
                if ( $columns[$i] == "version" ) {
                    // Special output formatting for 'version' column
                    $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
                }
                else if ( $columns[$i] != ' ' ) {
                    $row[] = $aRow[ $columns[$i] ];
                }
            }
            $output['data'][] = $row;
        }

        echo json_encode($this->utf8ize($output));
    }

}

header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');

// Create instance of TableData class
$tableData = new DataTables();
$tableData->get('clans', 'id', array('id', 'clanid', 'name'));

?>

更新

我刚刚注意到，当分页显示正确的数据时，控制台正在获取新的数据，但是这些数据并没有被放到表上。

Answer 1

查看您的php代码，您将使用LIMITing查询

$sLimit = "LIMIT 0, 100";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
}

但是，查看ajax调用的参数，您应该从以下位置读取页面和page_length：

长度- 10 起点-0

所以，您的代码应该如下所示

$result = $db->query("SELECT * FROM table WHERE field = 'value';");
$rows = array();
foreach($result AS $row){
    $rows[] = array(/*save your desired fields*/);
}
//sort them as you need
$iTotalRecords = $result->rowCount();
$iDisplayLength = intval($_REQUEST['length']);
$iDisplayStart = intval($_REQUEST['start']);
$iDisplayLength = $iDisplayLength < 0 ? $iTotalRecords : $iDisplayLength;
$end = $iDisplayStart + $iDisplayLength;
$end = $end > $iTotalRecords ? $iTotalRecords : $end;
$output = array(
    "draw" => 1,
    "recordsTotal" => $iTotal,
    "recordsFiltered" => $iFilteredTotal,
    "data" => array()
);
for($i = $iDisplayStart; $i < $end; $i++) {
    $output["data"][] = array(/*your table columns here*/);
}
$output["draw"] = $_REQUEST['draw'];
$output["recordsTotal"] = $iTotalRecords;
$output["recordsFiltered"] = $iTotalRecords;
echo json_encode($output);