How to use ThinkPHP to integrate datatables to implement server-side paging

The following is a sample code for ThinkPHP to integrate datatables to implement server-side paging. It has a good reference value and I hope it will be helpful to everyone.

There is a need to make something recently, because the amount of data is huge. Here I decided to use the server-side paging of datatables, and also need to pass the query conditions to the server. Most of the articles searched on the Internet felt that there were some errors, so I encapsulated them myself. The main configuration/tools are:

Server: php (using thinkphp)

The page style comes from H- ui framework (datatables version is 1.10.0)

The main modified (databases) configuration items are:

1) bProcessing:true using ajax Source

2) serverSide:true Use server-side paging

3) createdRow:function(){} callback function, used to add event or class name

4) aoColumns Used to process and display data, in which the render attribute is used for custom columns

1. The js code of datatables is:

$('.table-sort').dataTable({
 processing: true,
  serverSide: true,
  ajax: {
   "url":"{:U('Msg/index')}",
   "data":function(d){ //额外传递的参数
    d.mintime = $('#logmin').val();
    d.maxtime = $('#logmax').val();
   }
  },
  bStateSave: true,//状态保存
  aLengthMenu : [20, 30, 50, 100, 150],
  bProcessing : true,
  bAutoWidth: false,
  bFilter : true, //是否启动过滤、搜索功能
  bInfo : true, //是否显示页脚信息，DataTables插件左下角显示记录数 
  createdRow: function ( row, data, index ) {
   $(row).addClass('text-c');
   $('#count').html(data.recordsFiltered);
  },
  aoColumns: [
   {
    "sClass": "text-center",
    "data": "id",
    "render": function (data, type, full, meta) {
     return &#39;<input type="checkbox" name="select" value="&#39; + data + &#39;" />&#39;;
    },
    "bSortable": false
   },
   { "mData": "id" },
   { "mData": "fromnickname"},
   { "mData": "content" },
   { "mData": "msgtype" },
   { "mData": "time"},
   {
    "sClass": "text-center",
    "data": "id",
    "render": function (data, type, full, meta) {
     html = &#39;<a title="查看" href="javascript:;" rel="external nofollow" rel="external nofollow" onclick="show(`查看`,`__URL__/show/id/&#39;+ data + &#39;`,``,`610`)" class="ml-5" style="text-decoration:none"><i class="Hui-iconfont">查看</i></a>&#39;;
     html += &#39;<a style="text-decoration:none" class="ml-5" onClick="signDel(this,&#39;+ data +&#39;)" href="javascript:;" rel="external nofollow" rel="external nofollow" title="删除"><i class="Hui-iconfont"></i>删除</a>&#39;;
     return html;
    },
    "bSortable": false
   }
  ]
});

2. Server side:

##

控制器:
接收参数如下:
draw    前端传过来的值,原值返回,用于验证
mintime、maxtime 自定义参数(时间)
search.value  datatables搜索框参数,用于查询筛选
order.0.column  要排序的单元格(从0开始,字段需要自己设置)
order.0.dir   排序(升序、降序)
start    起始条数(第几条开始)
length    查询长度
返回的数据如下:
draw    返回前端传过来的值
recordsTotal  记录总条数
recordsFiltered  条件筛选后的记录总条数
data    服务端查询的数据
返回数据形式:json

3. The complete code of the server backend is as follows:

1) Controller code:

public function index()
{
 if(IS_AJAX){
  $list = D('Msg')->getData(I('get.'));
  $this->ajaxReturn($list);
 }
 $this->display();
}

2) Model layer code: (*Among them, the dealTime method is mainly used to process time periods)

public function getData($data)
{
 //获取Datatables发送的参数 必要
 $draw = $data['draw']; //这个值直接返回给前台
 //获取时间区间
 $timeArr['mintime'] = $data['mintime'];
 $timeArr['maxtime'] = $data['maxtime'];
 $where = $this->dealTime($timeArr);
 //搜索框
 $search = trim($data['search']['value']); //获取前台传过来的过滤条件 
 if(strlen($search) > 0) {
  $where['id|fromnickname|content|msgtype'] = array('like','%'.$search.'%');
 }
 //定义查询数据总记录数sql
 $recordsTotal = $this->count();
 //定义过滤条件查询过滤后的记录数sql
 $recordsFiltered = $this->where($where)->count();
 //排序条件
 $orderArr = [1=>'id', 2=>'fromnickname', 3=>'content', 4=>'msgtype', 5=>'time'];
 //获取要排序的字段
 $orderField = (empty($orderArr[$data['order']['0']['column']])) ? 'id' : $orderArr[$data['order']['0']['column']];
 //需要空格,防止字符串连接在一块
 $order = $orderField.' '.$data['order']['0']['dir'];
 //按条件过滤找出记录
 $result = [];
 //备注:$data['start']起始条数 $data['length']查询长度
 $result = $this->field('id,fromnickname,content,msgtype,time')
     ->where($where)
     ->order($order)
     ->limit(intval($data['start']), intval($data['length']))
     ->select();
 //处理数据
 if(!empty($result)) {
  foreach ($result as $key => $value) {
   $result[$key]['time'] = date("Y-m-d H:i:s",$value['time']);
   $result[$key]['recordsFiltered'] = $recordsFiltered;
  }
 }
 //拼接要返回的数据
 $list = array(
  "draw" => intval($draw),
  "recordsTotal" => intval($recordsTotal),
  "recordsFiltered"=>intval($recordsFiltered),
  "data" => $result,
 );
 return $list;
}

3) Implement custom ajax search

1. 在WdatePicker中添加onpicked回调函数
2. 执行table.fnFilter(),其中table为datatables对象

Take the WdatePicker plug-in as an example (the input box is similar, just bind the onchange event):

<input type="text" onfocus="WdatePicker({maxDate:&#39;#F{ $dp.$D(\&#39;logmax\&#39;)||\&#39;%y-%M-%d\&#39;}&#39;, onpicked:function(){table.fnFilter();}})" name="mintime" id="logmin" class="input-text Wdate" style="width:120px;">

3. The data attribute in the ajax attribute in datatables defines additional parameters to be passed

Example:

ajax: {
  "url":"{:U('Msg/index')}",
  "data":function(d){ //额外传递的参数
   d.mintime = $('#logmin').val();
   d.maxtime = $('#logmax').val();
  }

4) Code screenshot:

a. html page

b.js part

##The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Analysis of the method of implementing paging function in thinkPHP5

Analysis on the implementation of static caching and dynamic caching in ThinkPHP

The above is the detailed content of How to use ThinkPHP to integrate datatables to implement server-side paging. For more information, please follow other related articles on the PHP Chinese website!