结合daterangepicker实现Datatables表格带参数查询

此例子为参加 Datatables中文网第一期交流学习活动 网友 飘絮 提供的Demo 原网址
演示了怎么结合第三方插件( daterangepicker时间插件 )来作为额外条件更新表格数据

注意:当选择时间后,dt会重新请求服务器并带上日期这个参数,在控制台有打印,f12看network搜索server_processing_custom也可以看到url后带了自定义的参数 extra_search
由于本例只是做演示使用,后台并没有接受这个参数处理,所以数据没有变化

Datatables中文网提醒例子涉及到的: ajax.params()API , ajax.reload()API , ajax.dataOption

First name Last name Position Office Start date Salary
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
var table;
   $(function () {
       table = $('#example').DataTable({
           "ajax": {
               "url":"/example/resources/server_processing_customCUrl.php",
               "data": function ( d ) {
                   //添加额外的参数传给服务器
                   d.extra_search = $('#reportrange span').html();
               }},
           "processing": true,
           "serverSide": true,
           "language": {
               "sProcessing": "处理中...",
               "sLengthMenu": "显示 _MENU_ 项结果",
               "sZeroRecords": "没有匹配结果",
               "sInfo": "显示第 _START_ 至 _END_ 项结果,共 _TOTAL_ 项",
               "sInfoEmpty": "显示第 0 至 0 项结果,共 0 项",
               "sInfoFiltered": "(由 _MAX_ 项结果过滤)",
               "sInfoPostFix": "",
               "sSearch": "搜索:",
               "sUrl": "",
               "sEmptyTable": "表中数据为空",
               "sLoadingRecords": "载入中...",
               "sInfoThousands": ",",
               "oPaginate": {
                   "sFirst": "首页",
                   "sPrevious": "上页",
                   "sNext": "下页",
                   "sLast": "末页"
               },
               "oAria": {
                   "sSortAscending": ": 以升序排列此列",
                   "sSortDescending": ": 以降序排列此列"
               }
           },
           "dom":
                   "<'row'<'span9'l<'#mytoolbox'>><'span3'f>r>"+
                   "t"+
                   "<'row'<'span6'i><'span6'p>>"  ,
           initComplete:initComplete
       });
 
   });
 
   /**
    * 表格加载渲染完毕后执行的方法
    * @param data
    */
   function initComplete(data){
 
       var dataPlugin =
               '<div id="reportrange" class="pull-left dateRange" style="width:400px;margin-left: 10px"> '+
               '日期:<i class="glyphicon glyphicon-calendar fa fa-calendar"></i> '+
               '<span id="searchDateRange"></span>  '+
               '<b class="caret"></b></div> ';
       $('#mytoolbox').append(dataPlugin);
       //时间插件
       $('#reportrange span').html(moment().subtract('hours', 1).format('YYYY-MM-DD HH:mm:ss') + ' - ' + moment().format('YYYY-MM-DD HH:mm:ss'));
 
       $('#reportrange').daterangepicker(
               {
                   // startDate: moment().startOf('day'),
                   //endDate: moment(),
                   //minDate: '01/01/2012',    //最小时间
                   maxDate : moment(), //最大时间
                   dateLimit : {
                       days : 30
                   }, //起止时间的最大间隔
                   showDropdowns : true,
                   showWeekNumbers : false, //是否显示第几周
                   timePicker : true, //是否显示小时和分钟
                   timePickerIncrement : 60, //时间的增量,单位为分钟
                   timePicker12Hour : false, //是否使用12小时制来显示时间
                   ranges : {
                       //'最近1小时': [moment().subtract('hours',1), moment()],
                       '今日': [moment().startOf('day'), moment()],
                       '昨日': [moment().subtract('days', 1).startOf('day'), moment().subtract('days', 1).endOf('day')],
                       '最近7日': [moment().subtract('days', 6), moment()],
                       '最近30日': [moment().subtract('days', 29), moment()]
                   },
                   opens : 'right', //日期选择框的弹出位置
                   buttonClasses : [ 'btn btn-default' ],
                   applyClass : 'btn-small btn-primary blue',
                   cancelClass : 'btn-small',
                   format : 'YYYY-MM-DD HH:mm:ss', //控件中from和to 显示的日期格式
                   separator : ' to ',
                   locale : {
                       applyLabel : '确定',
                       cancelLabel : '取消',
                       fromLabel : '起始时间',
                       toLabel : '结束时间',
                       customRangeLabel : '自定义',
                       daysOfWeek : [ '日', '一', '二', '三', '四', '五', '六' ],
                       monthNames : [ '一月', '二月', '三月', '四月', '五月', '六月',
                           '七月', '八月', '九月', '十月', '十一月', '十二月' ],
                       firstDay : 1
                   }
               }, function(start, end, label) {//格式化日期显示框
 
                   $('#reportrange span').html(start.format('YYYY-MM-DD HH:mm:ss') + ' - ' + end.format('YYYY-MM-DD HH:mm:ss'));
               });
 
       //设置日期菜单被选项  --开始--
       var dateOption ;
       if("${riqi}"=='day') {
           dateOption = "今日";
       }else if("${riqi}"=='yday') {
           dateOption = "昨日";
       }else if("${riqi}"=='week'){
           dateOption ="最近7日";
       }else if("${riqi}"=='month'){
           dateOption ="最近30日";
       }else if("${riqi}"=='year'){
           dateOption ="最近一年";
       }else{
           dateOption = "自定义";
       }
       $(".daterangepicker").find("li").each(function (){
           if($(this).hasClass("active")){
               $(this).removeClass("active");
           }
           if(dateOption==$(this).html()){
               $(this).addClass("active");
           }
       });
       //设置日期菜单被选项  --结束--
 
 
       //选择时间后触发重新加载的方法
       $("#reportrange").on('apply.daterangepicker',function(){
           //当选择时间后,出发dt的重新加载数据的方法
           table.ajax.reload();
           //获取dt请求参数
           var args = table.ajax.params();
           console.log("额外传到后台的参数值extra_search为:"+args.extra_search);
       });
 
       function getParam(url) {
           var data = decodeURI(url).split("?")[1];
           var param = {};
           var strs = data.split("&");
 
           for(var i = 0; i<strs.length; i++){
               param[strs[i].split("=")[0]] = strs[i].split("=")[1];
           }
           return param;
       }
   }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<table id="example" class="table table-striped table-bordered">
       <thead>
       <tr>
           <th>First name</th>
           <th>Last name</th>
           <th>Position</th>
           <th>Office</th>
           <th>Start date</th>
           <th>Salary</th>
       </tr>
       </thead>
       <tbody></tbody>
       <!-- tbody是必须的 -->
   </table>
F12查看
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
<?php
/**
 * Created by PhpStorm.
 * User: dt.thxopen.com
 * Date: 2014/12/7
 * Time: 11:13
 */
try {
    //连接数据库
    $db = new SQLite3('datatables.sqlite3');
} catch (PDOException $e) {
    fatal(
        "数据库连接出错" . $e->getMessage()
    );
}
 
//获取Datatables发送的参数 必要
$draw = $_GET['draw'];//这个值作者会直接返回给前台
 
//排序
$order_column = $_GET['order']['0']['column'];//那一列排序,从0开始
$order_dir = $_GET['order']['0']['dir'];//ase desc 升序或者降序
 
//拼接排序sql
$orderSql = "";
if(isset($order_column)){
    $i = intval($order_column);
    switch($i){
        case 0;$orderSql = " order by first_name ".$order_dir;break;
        case 1;$orderSql = " order by last_name ".$order_dir;break;
        case 2;$orderSql = " order by position ".$order_dir;break;
        case 3;$orderSql = " order by office ".$order_dir;break;
        case 4;$orderSql = " order by start_date ".$order_dir;break;
        case 5;$orderSql = " order by salary ".$order_dir;break;
        default;$orderSql = '';
    }
}
//搜索
$search = $_GET['search']['value'];//获取前台传过来的过滤条件
 
//分页
$start = $_GET['start'];//从多少开始
$length = $_GET['length'];//数据长度
$limitSql = '';
$limitFlag = isset($_GET['start']) && $length != -1 ;
if ($limitFlag ) {
    $limitSql = " LIMIT ".intval($start).", ".intval($length);
}
 
//定义查询数据总记录数sql
$sumSql = "SELECT count(id) as sum FROM DATATABLES_DEMO";
//条件过滤后记录数 必要
$recordsFiltered = 0;
//表的总记录数 必要
$recordsTotal = 0;
$recordsTotalResult = $db->query($sumSql);
while ($row = $recordsTotalResult->fetchArray(SQLITE3_ASSOC)) {
    $recordsTotal $row['sum'];
}
//定义过滤条件查询过滤后的记录数sql
$sumSqlWhere =" where first_name||last_name||position||office||start_date||salary LIKE '%".$search."%'";
if(strlen($search)>0){
    $recordsFilteredResult = $db->query($sumSql.$sumSqlWhere);
    while ($row = $recordsFilteredResult->fetchArray(SQLITE3_ASSOC)) {
        $recordsFiltered $row['sum'];
    }
}else{
    $recordsFiltered = $recordsTotal;
}
 
//query data
$totalResultSql = "SELECT first_name,last_name,position,office,start_date,salary FROM DATATABLES_DEMO";
$infos = array();
if(strlen($search)>0){
    //如果有搜索条件,按条件过滤找出记录
    $dataResult = $db->query($totalResultSql.$sumSqlWhere.$orderSql.$limitSql);
    while ($row = $dataResult->fetchArray(SQLITE3_ASSOC)) {
        $obj = array($row['first_name'], $row['last_name'], $row['position'], $row['office'], $row['start_date'], $row['salary']);
        array_push($infos,$obj);
    }
}else{
    //直接查询所有记录
    $dataResult = $db->query($totalResultSql.$orderSql.$limitSql);
    while ($row = $dataResult->fetchArray(SQLITE3_ASSOC)) {
        $obj = array($row['first_name'], $row['last_name'], $row['position'],$row['office'], $row['start_date'], $row['salary']);
        array_push($infos,$obj);
    }
}
 
/*
 * Output 包含的是必要的
 */
echo json_encode(array(
    "draw" => intval($draw),
    "recordsTotal" => intval($recordsTotal),
    "recordsFiltered" => intval($recordsFiltered),
    "data" => $infos
),JSON_UNESCAPED_UNICODE);
 
 
function fatal($msg)
{
    echo json_encode(array(
        "error" => $msg
    ));
    exit(0);
}