首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >Jquery Datatables单列搜索

Jquery Datatables单列搜索
EN

Stack Overflow用户
提问于 2018-05-28 19:49:20
回答 2查看 2.7K关注 0票数 0

我正在尝试在Jquery中应用Individual column searching (select inputs) for Datatables插件。我正在使用服务器端处理,数据从SQL Server正确加载到我的表中,但当我想要选择一个输入来根据该值筛选列时,筛选器不能像您看到的here那样工作。当我选择一个国家(例如奥地利)时,数据不会被过滤到奥地利。这是我的索引页面

代码语言:javascript
复制
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html" charset=utf-8" />
  <title> Datatables Individual column searching using PHP Ajax Jquery </title>

<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap.min.css">

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>


 </head>
 <body>
  <div class="container">
   <h1 align="center">Datatables</h1>    
   <div class="table-responsive">
<table id="example" class="table table-striped table-bordered" style="width:100%">
        <thead>
            <tr>
                <th>Country</th>
                <th>Territory</th>
                <th>Market</th>
                <th>Entname</th>
                <th>MTM</th>
                <th>Qty</th>
            </tr>
        </thead>

        <tfoot>
            <tr>
                <th>Country</th>
                <th>Territory</th>
                <th>Market</th>
                <th>Entname</th>
                <th>MTM</th>
                <th>Qty</th>
            </tr>
        </tfoot>
    </table>
   </div>
  </div>
 </body>
</html>

<script type="text/javascript" language="javascript" >
$(document).ready(function() {

      var dtable = $('#example').DataTable({  
            "processing": true,
            "serverSide": true,
            "deferRender": true,
            "lengthMenu": [ [25, 50, 100, -1], [25, 50, 100, "All"] ],
            "pageLength": -1,
            "lengthChange": true,
            "pagingType": "full_numbers",
            "columns": [
                {"data": "Country"},
                {"data": "Territory"},
                {"data": "Market"},
                {"data": "Entname"},
                {"data": "MTM"},
                {"data": "Qty"}
            ],
            "ajax": {
                url: 'fetch.php',
                type: 'POST'
            },   
            initComplete: function () {
            this.api().columns().every( function () {
            var column = this;
            var select = $('<select><option value=""></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+'">'+d+'</option>' )
                } );
            } );
        }

        });

      $(".dataTables_filter input")
    .unbind() 
    .bind("keyup change", function(e) { 
        if(this.value.length >= 1 && e.keyCode == 13) { dtable.search(this.value).draw(); }
        if(this.value == "") { dtable.search("").draw(); }
        return;
    });

} );
</script>

这是我的fetch.php

代码语言:javascript
复制
<?php 
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


if (!empty($_POST) ) {

$ser="*****";
$db="*****";
$user="*****"; 
$pass="*****";
$dbDB = new PDO("odbc:Driver=ODBC Driver 13 for SQL Server;Server=*****;Database=*****;Port=1456", $user, $pass);
$MyTable="*****";

    function getData($sql){
        global $dbDB ;
        global $MyTable ;
        $result = $dbDB->query($sql);       
        $data= $result->fetchAll(PDO::FETCH_ASSOC);
        return $data; }

        $res = $dbDB->query("SELECT COUNT(*) FROM ".$MyTable);
        $recordsTotal = $res->fetchColumn();

    $draw = $_POST["draw"];
    $orderByColumnIndex  = $_POST['order'][0]['column'];
    $orderBy = $_POST['columns'][$orderByColumnIndex]['data'];
    $orderType = $_POST['order'][0]['dir']; 
    $start  = $_POST["start"];

    if ($_POST['length'] == -1) {$length = $recordsTotal;} 
    else {$length = $_POST['length'];}    


    if(!empty($_POST['search']['value'])){

        for($i=0 ; $i<count($_POST['columns']);$i++){
            $column = $_POST['columns'][$i]['data'];
            $where[]="$column like '%".$_POST['search']['value']."%'";
        }
        $where = "WHERE ".implode(" OR " , $where);


$sql = sprintf("SELECT COUNT(*) FROM %s %s", $MyTable , $where);
$r = $dbDB->query($sql);
$recordsFiltered = $r->fetchColumn();

         $sql = sprintf("SELECT Country,Territory,Market,Entname,MTM,Qty FROM %s %s ORDER BY %s %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", $MyTable , $where ,$orderBy, $orderType ,$start,$length);
        $data = getData($sql);
    }

    else {

        $sql = sprintf("SELECT Country,Territory,Market,Entname,MTM,Qty FROM %s ORDER BY %s %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", $MyTable ,$orderBy, $orderType ,$start,$length);
          $data = getData($sql);
        $recordsFiltered = $recordsTotal;
    }

        $response = array(
        "draw" => intval($draw),
        "recordsTotal" => $recordsTotal,
        "recordsFiltered" => $recordsFiltered,
        "data" => $data );

    echo json_encode($response);
} 

else {
    echo "NO POST Query from DataTable";
}

?>

我认为问题出在javascript部分的某个地方,而不是php文件,而仅仅是为了显示数据是如何填充到表中的概述。

您知道问题出在哪里吗?

EN

回答 2

Stack Overflow用户

发布于 2018-05-28 20:05:01

检查下面的示例

代码语言:javascript
复制
   // Setup - add a text input to each footer cell
 $('#item tfoot th').each( function () {
     var title = $(this).text();
     $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
 } );

// DataTable
 var otable = $('#item').DataTable();

 // Apply the search
 otable.columns().every( function () {
 
     var that = this;
     $( 'input', this.footer() ).on( 'keyup change', function () {
         if ( that.search() !== this.value ) {
             that
                 .search( this.value )
                 .draw();
         }
     } );
 } );
代码语言:javascript
复制
 tfoot {
    display: table-header-group;
}
代码语言:javascript
复制
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://www.jqueryscript.net/demo/DataTables-Jquery-Table-Plugin/media/css/jquery.dataTables.css">

<table name="item" id="item" class="display" width="100%" cellspacing="0">
  <thead>
           <tr>
               <th>Name</th>
               <th>Position</th>
               <th>Office</th>
               <th>Age</th>
               <th>Start date</th>
               <th>Salary</th>
           </tr>
       </thead>
       <tfoot>
           <tr>
               <th>Name</th>
               <th>Position</th>
               <th>Office</th>
               <th>Age</th>
               <th>Start date</th>
               <th>Salary</th>
           </tr>
       </tfoot>
       <tbody>
           <tr>
               <td>Tiger Nixon</td>
               <td>System Architect</td>
               <td>Edinburgh</td>
               <td>61</td>
               <td>2011/04/25</td>
               <td>$320,800</td>
           </tr>
           <tr>
               <td>Garrett Winters</td>
               <td>Accountant</td>
               <td>Tokyo</td>
               <td>63</td>
               <td>2011/07/25</td>
               <td>$170,750</td>
           </tr>
           <tr>
               <td>Ashton Cox</td>
               <td>Junior Technical Author</td>
               <td>San Francisco</td>
               <td>66</td>
               <td>2009/01/12</td>
               <td>$86,000</td>
           </tr>
           <tr>
               <td>Cedric Kelly</td>
               <td>Senior Javascript Developer</td>
               <td>Edinburgh</td>
               <td>22</td>
               <td>2012/03/29</td>
               <td>$433,060</td>
           </tr>
           <tr>
               <td>Airi Satou</td>
               <td>Accountant</td>
               <td>Tokyo</td>
               <td>33</td>
               <td>2008/11/28</td>
               <td>$162,700</td>
           </tr>
      </tbody>
          
   </table>

票数 0
EN

Stack Overflow用户

发布于 2021-02-09 18:20:09

datefilter搜索怎么样,也就是enddate>startdate到这个datatable.It会更方便。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50565742

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档