javascript - PHP - DataTables 服务器端单独的列过滤仅提供第一页选择选项

Question

我设置了我的 DataTable，从我的数据库正确返回数据并且过滤确实有效，但我遇到的问题是每列的选择框只显示第一页数据之外的值。

我想我必须在我的 PHP 中执行某种类型的 DISTINCT 查询，但我不知道如何将其导入 JavaScript 以及如何使用 DataTables。

这是我目前所拥有的:

HTML/JS:

<table id="mainTable" class="table table-hover table-bordered table-striped table-condensed" >
    <thead>
        <tr>
            <th>ID</th>
            <th>UserID</th>
        </tr>
    </thead>

    <tfoot>
        <tr>
            <th>ID</th>
            <th>UserID</th>
        </tr>
    </tfoot>


</table>  

<script type="text/javascript" charset="utf-8">
            $(document).ready(function() {

            $('#mainTable').dataTable( {

            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "<?php echo url(); ?>/new/datatables.php?sSearch=1&bSearchable_0=1&bSearchable_1=1&sEcho=1&sOrder=1",

            initComplete: function () {
            var api = this.api();

            api.columns().indexes().flatten().each( function ( i ) {
                var column = api.column( i );
                var select = $('<select><option value="">All</option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );

                        column
                            .search( val ? ''+val+'' : '', true, false )
                            .draw();
                    } );

                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'" style="min-width:95px;">'+d+'</option>' )
                } );
            } );
        },


                    "aLengthMenu": [20, 10, 20, 25, 50],
                    "oLanguage": {
                    "sLoadingRecords": "<h4 style='float:left;'>Loading...</h4> <img src='<?php echo url(); ?>/img/common/loading3.gif' style='float:right;'>"
                                            },

                    "pagingType": "full_numbers",

                    "sDom": "<'container-fluid'<'span6'<'pull-left'T>><'span6'<'pull-right'f>>r>t<'clear'>rt<'container-fluid'<'span6'<'pull-left'i>><'span6'<'pull-right'p>>>",

                  "oTableTools": {
                  "sSwfPath": "<?php echo url(); ?>/new/app/DataTables/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
                  "aButtons": [
                {
                    "sExtends": "csv",
                    "sFileName": "Pick Lines - *.csv",
                    "sButtonText": "Export to CSV",
                },

                {
                    "sExtends": "xls",
                    "sFileName": "Pick Lines - *.xls",
                    "sButtonText": "Export to EXCEL (xls)",
                },

                "copy", "print"

                                    ]
                    }



                } );
            } );
        </script>

PHP:

<?php

$serverName = ""; //serverName\instanceName
$connectionInfo = array( "Database"=>"", "UID"=>"", "PWD"=>"");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

    /*
     * Script:    DataTables server-side script for PHP and MySQL
     * Copyright: 2010 - Allan Jardine
     * License:   GPL v2 or BSD (3-point)
     */

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */

    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */


    $aColumns = array( 'ID', 'UserID');

    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "ID";

    /* DB table to use */
    $sTable = "ActivityLog";


    /* 
     * Paging
     */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
    $sLimit = "OFFSET  ".$_GET['iDisplayStart']." ROWS 
                                FETCH NEXT ".$_GET['iDisplayLength']." ROWS ONLY "; 
    }


    /*
     * 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" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".addslashes( $_GET['sSortDir_'.$i] ) .", ";
            }
        }

        $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 ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {

        $_GET['bSearchable_'.$i] = "true";
        if ( isset($_GET['sSearch_'.$i]) && !empty($_GET['sSearch_'.$i]))
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['sSearch_'.$i])."%' ";
        }
        else {
             $sWhere .= "";
        }
    }


    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
SELECT ID, UserID
FROM
$sTable
                 $sWhere
                 $sOrder
                 $sLimit
    ";



    $rResult = sqlsrv_query($conn,  $sQuery ) or die(print_r(sqlsrv_errors()));

    /* Data set length after filtering */
    $sQueryRow = "
SELECT ID, UserID  FROM $sTable
                 $sWhere
    ";

    //echo $sQueryRow;
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $stmt = sqlsrv_query( $conn, $sQueryRow , $params, $options );

    $iFilteredTotal = sqlsrv_num_rows( $stmt );



    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = sqlsrv_query($conn,  $sQuery ) or die(print_r(sqlsrv_errors()));
    $aResultTotal = sqlsrv_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];




    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode( $output );
?>

我遇到的问题是它显示了 39 行，这是应该的，但是下拉列表只显示每个过滤器第一页上的前 20 行，就像在这个图像示例中，39 行但只有 1-20 到在下拉框上过滤。

当我进入下一页时，它仍然只显示前 20 个。

Answer 1

我对此进行了一些研究，但找不到任何示例代码，因此我提出了一个运行良好的解决方案。为了解释它的工作原理，我创建了一个函数，该函数在选择下拉的重点关注时会进行AJAX调用。我还向 datatables php 添加了一个 where 子句来处理过滤后的 json 数据的显示。

需要注意的一件重要事情是，我必须建立流量控制，因为如果使用其他 tabletools 元素，则在通过列进行过滤时会发生一些奇怪的行为。我的解决方案是清除使用表格工具时选择的过滤，以便监听大量事件。

HTML

    <script type="text/javascript" language="javascript" src="./jquery-1.11.1.min.js"></script>
    <script type="text/javascript" language="javascript" src="./jquery.dataTables.min.js"></script> 
    <script type="text/javascript" language="javascript" src="./dataTables.tableTools.min.js"></script> 
    <script type="text/javascript" language="javascript" class="init">

var where;
var selInitd = new Array();
var selectedData;

$(document).ready(function() {
   function getSelectedList(str){
    var response = new Array();
    var returnArray = new Array();
    $.ajax({
        url: "./filter.php",
        data: "selected="+str,
        async: false,
        success: function(msg){
            response = msg.split(',');
            //alert(response);
            for(m = 0; m < response.length; m++){
              response[m] = response[m].replace('[','');
              response[m] = response[m].replace(']','');
              response[m] = response[m].replace(/\"/g,'');

              var val = response[m];
              returnArray.push(val);
            }
        }
    })

    return returnArray;
   } 

   var columnNumber = $('#example thead th').length;

   //this can probably be handled in PHP 
   function colSelected(column){
    switch(column){
        case 0:
            return "columnA";
            break;
        case 1:
            return "columnB";
            break;
        case 2:
            return "columnC";
            break;
        case 3:
            return "columnD";
            break;
        case 4:
            return "columnE";
            break;
        case 5:
            return "columnF";
            break;
        case 6:
            return "columnG";
            break;
        case 7:
            return "columnH";
            break;
        case 8:
            return "columnI";
            break;  
        case 9:
            return "columnJ";
            break;  
        default:
            return "columnA";
    }
   } 

  var table = $('#example')
    .DataTable( {
        scrollX: true,
        dom: '<"top"fl>rt<"bottom"ipT><"clear">',
                serverSide: true,
        processing: true,
        ordering: true,
        order: [[ 9, "columnJ" ]],
        ajax: {
            url: "../dt.php",
            data: function ( json ){
                json.where = where;
            },
        },
        tableTools: {
            sRowSelect: "os",
            sSwfPath: "./copy_csv_xls_pdf.swf",
            aButtons: [
                {
                  sExtends: "collection",
                  sButtonText: "Save",
                  sButtonClass: "save-collection",
                  aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        },
        initComplete: function (settings, json) {
            var api = this.api();
            //alert(Object.keys(settings.oPreviousSearch));

            var col;

            api.columns().indexes().flatten().each( function ( i ) {
                selInitd[i] = false;
                var column = api.column( i );
                    //made the column number the id for getElementById
                var select = $('<select id=' + i + '><option value=""></option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        for (k = 0; k < columnNumber; k++){
                            //inefficient but gets every column selected every time any column is selected
                            var value = document.getElementById(k).value;
                            if (value != "" ){

                                //exposes the column names, you probably want to do this differently
                                col = colSelected(k);

                                //build where to send to php, you probably want to do this differently
                                if (where == null){
                                    where = col + " = '" + value + "'";
                                }else{
                                    where = where + " AND " + col + " = '" + value + "'";
                                }
                            }
                        }

                        api.ajax.reload( null, true );

                        where = null;

                    } )
                    .on( 'focus', function () {
                        if(!selInitd[i]){
                            var selectedL = getSelectedList(colSelected(i)).slice();

                            for(d = 0; d < selectedL.length; d++) {
                                select.append( '<option value="' + selectedL[d] + '">' + selectedL[d] + '</option>' )
                            }

                            selInitd[i] = true;
                        }
                    } );
             } );
        }
    } );

    //reset filter selections when text is entered in the search box
    $(".dataTables_filter input")
        .on("input.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    //reset filter selections when sorting by column
    $(".dataTables_scrollHeadInner th.sorting")
        .on("click.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    //reset filter selections page length widget is changed
    $(".dataTables_length select")
        .on("click.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    //reset filter selections page number is changed     
    table.on("page.dt", function(e) {
        //reset select inputs
        for(h = 0; h < columnNumber; h++){
            var value = document.getElementById(h);
            value.selectedIndex = 0;
        }

        return;
    });
} );

    </script>
</head>

</body>
<div class="container">
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
            <th>columnA</th>
            <th>columnB</th>
            <th>columnC</th>
            <th>columnD</th>
            <th>columnE</th>
            <th>columnF</th>
            <th>columnG</th>
            <th>columnH</th>
            <th>columnI</th>
            <th>columnJ</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
            <th>columnA</th>
            <th>columnB</th>
            <th>columnC</th>
            <th>columnD</th>
            <th>columnE</th>
            <th>columnF</th>
            <th>columnG</th>
            <th>columnH</th>
            <th>columnI</th>
            <th>columnJ</th>
            </tr>
        </tfoot>
    </table>
</div>
</body>
</html>

过滤 PHP

<?php
$column = $_GET['selected'];

//conection: 
$link = mysqli_connect("ip.address","user","password","DB") or die("Error " . mysqli_error($link)); 

//consultation: 

$query = "SELECT DISTINCT ".$column." FROM table ORDER BY ".$column." ASC" or die("Error in the consult.." . mysqli_error($link)); 

//execute the query. 

$result = $link->query($query); 

//display information: 

$rows = array();
$rIdx = 0;

while($row = mysqli_fetch_array($result)) { 
  $rows[$rIdx] =  $row[$column]; 
  $rIdx++;
} 

if($rows){
    echo json_encode($rows);
}

DTPHP

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'table';

// Table's primary key
$primaryKey = 'columnA';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'columnA',   'dt' => 0 ),
    array( 'db' => 'columnB',   'dt' => 1 ),
    array( 'db' => 'columnC',   'dt' => 2 ),
    array( 'db' => 'columnD',   'dt' => 3 ),
    array( 'db' => 'columnE',   'dt' => 4 ),
    array( 'db' => 'columnF',   'dt' => 5 ),
    array( 'db' => 'columnG',   'dt' => 6 ),
    array( 'db' => 'columnH',   'dt' => 7 ),
    array( 'db' => 'columnI',   'dt' => 8 ),
    array( 'db' => 'columnJ',   'dt' => 9 )
);

// SQL server connection information
$sql_details = array(
    'user' => 'user',
    'pass' => 'password',
    'db'   => 'DB',
    'host' => 'ip.address'
);

$whereAllClause = '';

if ($_GET['where'] != ''){
    $whereAllClause = $_GET['where'];
    $whereAllClause = str_replace('+', ' ', $whereAllClause);
    $whereAllClause = str_replace('%3D', '=', $whereAllClause);
    $whereAllClause = str_replace('%27', '\'', $whereAllClause);
}


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( './ssp.class.php' );

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $whereAllClause )
);