AJAX实现yii(原生sql)简单的时间区间搜索加分页

一· yii控制器页面:

  1 <?php
  2 namespace appcontrollers;
  3 
  4 use CodeceptionLibDriverDb;
  5 use yii;
  6 use yiiwebController;
  7 
  8 class SerchController extends Controller{
  9     //禁用curl
 10    public $enableCsrfValidation = false;
 11         public function actionIndex(){
 12 
 13              $page = yii::$app->request->post('page',1);
 14 
 15              $size = 3;
 16 
 17              $arr1 =  yii::$app->db->createCommand("select * from `wenzhang`")->queryAll();
 18 
 19              $count = count($arr1);
 20 
 21              $end = ceil($count/$size);
 22 
 23              $offset = ($page-1)*$size;
 24 
 25              $arr =  yii::$app->db->createCommand("select * from `wenzhang` limit $offset,$size")->queryAll();
 26 
 27 
 28              return $this->render('index',['list'=>$arr,'end'=>$end,'page'=>$page]);
 29 
 30 
 31 
 32         }
 33 
 34         public function actionShows(){
 35            //开始时间
 36             $begindate = yii::$app->request->post('beginDate');
 37             //结束时间
 38             $enddate = yii::$app->request->post('endDate');
 39 
 40 
 41             $page = yii::$app->request->post('page',1);
 42 
 43             $size = 3;
 44 
 45             $offset = ($page-1)*$size;
 46 
 47              //开始结束为空
 48                 if (empty($begindate) && empty($enddate)){
 49 
 50 
 51                     //  分页功能
 52                     $arr1 =  yii::$app->db->createCommand("select * from `wenzhang`")->queryAll();
 53 
 54                     $count = count($arr1);
 55 
 56                     $end = ceil($count/$size);
 57 
 58 
 59                     $sql = "select * from `wenzhang` limit $offset,$size";
 60 
 61                     $arr['data'] =  yii::$app->db->createCommand($sql)->queryAll();
 62                     $arr['end']  = $end;
 63                     echo json_encode($arr);
 64                     exit();
 65 
 66 
 67                 }else if ($begindate && $enddate){
 68 
 69                    //二者相等
 70                     if ($begindate == $enddate){
 71 
 72                       //sql查询
 73                         $sql2 = "select * from `wenzhang` where `publish_time` like '$enddate%' ORDER BY `publish_time` asc";
 74                         $sql1 = "select * from `wenzhang` where `publish_time` like '$enddate%' ORDER BY `publish_time` asc limit $offset,$size";
 75 
 76 
 77                     }else{
 78 
 79                      //否之查询
 80                         $sql2 = "SELECT * FROM wenzhang WHERE `publish_time` BETWEEN '$begindate' and '$enddate' OR `publish_time` LIKE '$enddate%' ORDER BY `publish_time` asc";
 81                         $sql1 = "SELECT * FROM wenzhang WHERE `publish_time` BETWEEN '$begindate' and '$enddate' OR `publish_time` LIKE '$enddate%' ORDER BY `publish_time` asc limit $offset,$size";
 82 
 83                     }
 84                     //调用查询函数
 85                     $arr1 = yii::$app->db->createCommand($sql2)->queryAll();
 86 
 87                     $arr2 = yii::$app->db->createCommand($sql1)->queryAll();
 88 
 89                     $count = count($arr1);
 90 
 91                     $end = ceil($count/$size);
 92 
 93 
 94                     $arr['data'] = $arr2;
 95                     $arr['end'] = $end;
 96 
 97                   //返回数据
 98                     echo json_encode($arr);
 99                     exit();
100                 }
101 
102 
103 
104 
105 
106         }
107 
108 
109 
110 }

二. JS(jq)

  1 <!doctype html>
  2 <html lang="en">
  3 <head>
  4     <meta charset="UTF-8">
  5     <meta name="viewport"
  6           content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  7     <meta http-equiv="X-UA-Compatible" content="ie=edge">
  8     <title>Document</title>
  9 </head>
 10 <body>
 11 发布时间:<input type="date" id="dateSerch1">至 <input type="date" id="dateSerch">
 12 <table class="table table-hover">
 13     <tr>
 14         <td>id</td>
 15         <td>日期</td>
 16         <td>标题</td>
 17         <td>作者</td>
 18     </tr>
 19     <tbody id="ti">
 20     <?php foreach ($list as $k => $v){ ?>
 21     <tr>
 22         <td><?= $v['id']?></td>
 23         <td><?= $v['publish_time']?></td>
 24         <td><?= $v['title']?></td>
 25         <td><?= $v['author']?></td>
 26     </tr>
 27     <?php } ?>
 28     </tbody>
 29 
 30 </table>
 31 <input type="hidden" value="1" id="sou">
 32 <input type="hidden" value="<?= $end ?>" id="end">
 33 <a href="#" class="cli-page">首页</a>
 34 <a href="#" class="cli-page">上一页</a>
 35 <a href="#" class="cli-page">下一页</a>
 36 <a href="#" class="cli-page">尾页</a>
 37 </body>
 38 </html>
 39 <script src="/js/jquery-3.3.1.js"></script>
 40 <script>
 41     //定义全局
 42     var date1;
 43     var date;
 44     //分页判断
 45        $(document).on("click",".cli-page",function () {
 46                var sou = $("#sou").val();
 47                var end = $("#end").val();
 48                var text = $(this).text();
 49 
 50                 if (text=='首页'){
 51 
 52                     var page = 1;
 53                 } else if (text=='上一页'){
 54                     page = parseInt(sou) - 1 > 0 ? parseInt(sou) - 1 : 1;
 55                 } else if (text == '下一页'){
 56                     page = parseInt(sou)+1 > end ? end : parseInt(sou)+1;
 57                 } else if (text=='尾页'){
 58                     page = end;
 59                 }
 60                 //发送全局变量
 61                 pages(page,date1,date);
 62 
 63 
 64        })
 65 
 66        function pages(page=null,date1=null,date=null){
 67 
 68             //ajax发送数据
 69            $.ajax({
 70                method : 'post',
 71                url : 'shows',
 72                dataType : 'json',
 73                data : {
 74                    page : page,
 75                    beginDate : date1,
 76                    endDate : date,
 77                },
 78                success:function (data) {
 79                    //替换
 80 
 81                    var str = '';
 82                    $.each(data.data,function (k,v) {
 83 
 84                        str +=
 85                            '        <tr>
'+
 86                            '        <td>'+v.id+'</td>
'+
 87                            '        <td>'+v.publish_time+'</td>
'+
 88                            '        <td>'+v.title+'</td>
'+
 89                            '        <td>'+v.author+'</td>
'+
 90                            '    </tr>'
 91 
 92                    })
 93                    //替换当前页与总页数
 94                    $("#ti").html(str);
 95 
 96                    $("#sou").val(page);
 97 
 98                    $("#end").val(data.end);
 99 
100                }
101            })
102 
103        }
104      //开始时间结束时间
105     $(document).on("change","#dateSerch",function () {
106 
107              date1 = $("#dateSerch1").val();
108             if (date1==''){
109                 return
110             }
111              date = $(this).val();
112             if (date==''){
113                 return
114             }
115             var page = $("#sou").val();
116       //调用公共方法传值
117             pages(page,date1,date)
118     })
119 </script>