AJAX实现yii(原生sql)简单的时间区间搜索加分页
一· yii控制器页面:
1 <?php 2 namespace app\controllers; 3 4 use Codeception\Lib\Driver\Db; 5 use yii; 6 use yii\web\Controller; 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>\n'+ 86 ' <td>'+v.id+'</td>\n'+ 87 ' <td>'+v.publish_time+'</td>\n'+ 88 ' <td>'+v.title+'</td>\n'+ 89 ' <td>'+v.author+'</td>\n'+ 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>
