DataTables

最新推荐文章于 2021-07-10 23:56:00 发布

冰凉的尾巴

最新推荐文章于 2021-07-10 23:56:00 发布

阅读量524

点赞数

本文链接：https://blog.csdn.net/vivienne_shar/article/details/67632816

版权

https://www.datatables.net/
中文网示例

<table id="example" class="display" cellspacing="0" width="100%">
        <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>
 
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
</table>

API实例可以通过以下方式创建：
$( selector ).DataTable();
$( selector ).dataTable().api();

var table;
var size = 10; // 每页显示数据条数
var timeout = 300000; // 定时时长,5min
$(function() {
    table = $("#example").dataTable({
        "processing": true,
        "serverSide": true, // 开启服务器模式
        "ajax": {
            "url": "/api",
            "dataType": "json"
        },
        "bLengthChange": false, // 可改变每页显示数量
        "bFilter": false,
        "bPaginate": true,
        "info": true, // 左下显示表格信息
        "bSort": false, // 排序
        "oLanguage": {
            "sLengthMenu": "每页显示 _MENU_ 条记录",
            "sZeroRecords": "没有查询到数据",
            "sInfo": "当前数据为从第 _START_ 到第 _END_ 条数据；总共有 _TOTAL_ 条记录",
            "sInfoEmtpy": "没有数据",
            "sProcessing": "正在加载数据...",
            "oPaginate": {
                "sFirst": "首页",
                "sPrevious": "上一页",
                "sNext": "下一页",
                "sLast": "尾页"
            }
        },
        "iDisplayLength": size, // 默认显示数据条数
        "columnDefs": [{
            "width": "70%" // 表格宽度
        }, {
            "width": "10%"
        }, {
            "width": "10%"
        }, {
            "width": "10%"
        }]
    });
    // 定时翻页  
    setInterval(function() {
        var pageinfo = table.api().page.info();
        /* 
         * 最后一页时 pageinfo.page < pageinfo.pages - 1
         * 总页数 pageinfo.pages
         * 当前页数 pageinfo.page
         */
        // 第5页时或最后一页时  
        if (pageinfo.page < 4 || pageinfo.page < pageinfo.pages - 1) {
            table.api().page('next').draw(false);
            /* first - 首页
             * next - 下一页
             * previous - 上一页
             * last - 末页
             */
        } else {
            /*
             * 返回第一页 table.api().page('first').draw(false);
             */
            // 刷新页面
            window.location.reload();
        }
        // 页面滚动对齐
        var ScrollOffset = 0;
        $("html, body").animate({
            scrollTop: $("#datatable").offset().top - ScrollOffset + "px"
        }, {
            duration: 500,
            easing: "linear"
        });
    }, timeout);
});

http://localhost/api?
draw=1&
columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&
columns[1][data]=1&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&
columns[2][data]=2&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&
columns[3][data]=3&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=true&columns[3][search][value]=&columns[3][search][regex]=false&
columns[4][data]=4&columns[4][name]=&columns[4][searchable]=true&columns[4][orderable]=true&columns[4][search][value]=&columns[4][search][regex]=false&
columns[5][data]=5&columns[5][name]=&columns[5][searchable]=true&columns[5][orderable]=true&columns[5][search][value]=&columns[5][search][regex]=false&
order[0][column]=0&
order[0][dir]=asc&
start=0&
length=10&
search[value]=&
search[regex]=false&
_=1418644693360

<?php
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);
}

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [
    [
      "Airi",
      "Satou",
      "Accountant",
      "Tokyo",
      "2008/11/28",
      162700
    ],
    [
      "Angelica",
      "Ramos",
      "Chief Executive Officer (CEO)",
      "London",
      "2009/10/09",
      1200000
    ],
    [
      "Ashton",
      "Cox",
      "Junior Technical Author",
      "San Francisco",
      "2009/01/12",
      86000
    ],
    [
      "Bradley",
      "Greer",
      "Software Engineer",
      "London",
      "2012/10/13",
      132000
    ],
    [
      "Brenden",
      "Wagner",
      "Software Engineer",
      "San Francisco",
      "2011/06/07",
      206850
    ],
    [
      "Brielle",
      "Williamson",
      "Integration Specialist",
      "New York",
      "2012/12/02",
      372000
    ],
    [
      "Bruno",
      "Nash",
      "Software Engineer",
      "London",
      "2011/05/03",
      163500
    ],
    [
      "Caesar",
      "Vance",
      "Pre-Sales Support",
      "New York",
      "2011/12/12",
      106450
    ],
    [
      "Cara",
      "Stevens",
      "Sales Assistant",
      "New York",
      "2011/12/06",
      145600
    ],
    [
      "Cedric",
      "Kelly",
      "Senior Javascript Developer",
      "Edinburgh",
      "2012/03/29",
      433060
    ]
  ]
}