基于bootstrap 的datatable插件的使用（php版）

Datatables是一款jquery表格插件。它是一个高度灵活的工具，可以将任何HTML表格添加高级的交互功能。
详细学习请参考其官网：http://datatables.net/中文网：http://dt.thxopen.com/

下面是我学习datatables写的一个服务器端分页例子，首先，上图：

2、接着是js代码了
     2.1显示初始化datatables，并撰写删除修改增加记录这几个功能按钮函数

　　

function initTable () {
            var table = $("#example").DataTable({ 
                "paging":true,
                "pagingType":"full_numbers",
                "lengthMenu":[5,10,25,50],                             
                "processing": true,
                "searching": true, //是否开启搜索
                "serverSide": true,//开启服务器获取数据
                "order": [[ 0, "desc" ]], //默认排序
                "ajax":{ // 获取数据
                    "url":"server.php",
                    "dataType":"json" //返回来的数据形式
                },
                "columns":[ //定义列数据来源
                            {'title':"id",'data':"teacher_id"},
                            {'title':"name",'data':"teacher_name"},
                            {'title':"pwd",'data':"teacher_password"},//隐藏
                            {'title':"email",'data':"teacher_email"},
                            {'title':"date",'data':"teacher_date"},
                            {'title':"note",'data':"teacher_note"},
                            {'title':"action",'data':null,'class':"align-center"} // 自定义列
                        ],
                "columnDefs": [ //自定义列
                            {
                              "targets": -1, //改写哪一列
                              "data": "teacher_id",
                              "render": function(data, type, row) {
                                // return "<a href='"+full.teacher_id+"'>look at my href</a>";
                                var html = "<button class='btn btn-info' onclick='edit("+row.teacher_id+")'>修改</button>" + "   <button name='btn-del' class='btn btn-danger' onclick='del("+row.teacher_id+")'>删除</button>";
                                return html;
                              }
                            },
                            {
                                "targets":2,
                                "visible":false //隐藏列
                            },
                            {
                                "targets":[-1,-2],
                                "orderable":false  //禁止排序
                            },
                            {
                                "targets":[-1,-3],
                                "searchable":false //禁止搜索
                            }
                          ],
                "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": ": 以降序排列此列"
                    }
                },
                "createdRow":function(row,data,index){ //行回调函数
                    if(data.teacher_id % 2 == 0){
                        $('td',row).eq(0).css({"color":"#00A8FF","font-weight":"bold"});
                    }
                }
                
            });//table end
            
            return table;
        } //inintTable END

        // 删除
        function del(id){
             if(confirm('你确定要删除 id='+id+' 的记录?')){
                 // 确定
                 var jsonData={
                     "id":id
                 };
                 $.ajax({
                     type:"get",
                     url:"del.php",
                     data:jsonData,
                     dataType:"json",
                     success:function(msg){
                         // var msg = eval(msg);
                         if(msg.status == true){
                             alert("删除 id= "+msg.id+"成功");
                             // 刷新table
                             // oTable.fnReloadAjax(); //1.9之前
                             oTable.ajax.reload(); //1.10之后
                             // oTable.draw();
                             
                         }else{
                             alert("删除失败");
                         }
                     },
                     error: function(error){
                         console.log(error);
                     }
                 });
             }else{
                 // 取消
                 return ;
             }
             
        }
        //增加
        function add () {
            // console.log("add action");
            var jsonData={
                "name":$("#add_name").val(),
                "pwd":$("#add_pwd").val(),
                "email":$("#add_email").val(),
                "date":$("#add_date").val(),
                "note":$("#add_note").val()
            };
            $.ajax({
                type:'get',
                url:'add.php',
                data:jsonData,
                dataType:"json",
                success:function(msg){
                    if(msg.status == true){
                        alert("添加成功");
                        oTable.ajax.reload();
                    }else{
                        alert("添加失败");
                    }
                },
                error:function(error){
                    console.log(error);

                }
                
            });
        }

        // 修改
        function edit(id){
             alert("修改 id = "+id);
        }

2.2 然后在页面加载完毕后开启datatables功能，并增加每列搜索的input框

　　

$(function(){
                // oTable 全局变量
                oTable = initTable();

                
                // 每列搜索框
                /*$("#search li").each(function(){
                    var title = $('#example thead th').eq( $(this).index() ).text();
                    $(this).html(title+'<input type="text" placeholder="Search '+title+'" class="form-control"/>');
                });

                $("#search li").each(function(index){
                    $(this).find('input').on( 'keyup change', function () {
                        oTable
                            .column(index)
                            .search( this.value )
                            .draw();
                    } );
                });*/

                // Setup - add a text input to each footer cell
                $('#example tfoot th').each( function (index) {
                    if(index == 3 || index == 5){return;}//隐藏某一列input
                    var title = $('#example thead th').eq( $(this).index() ).text();
                    $(this).html( '<input type="text" placeholder="Search '+title+'" class="form-control"/>' );
                } );
             
                // Apply the search
                oTable.columns().eq( 0 ).each( function ( colIdx ) {
                    $( 'input', oTable.column( colIdx ).footer() ).on( 'keyup change', function () {
                        oTable
                            .column( colIdx )
                            .search( this.value )
                            .draw();
                    } );
                } );

            });//jquery end

3、最后说server.php代码
基本思想就是接收传过来的参数，然后对参数进行处理，编写sql语句，然后取出当页数据返回页面。
传递过来的参数可以用firefox查看：

<?php 
header("Content-type: text/html;charset=utf-8");//防止乱码
//1、获取链接
$conn=@mysql_connect("localhost","root","");
if(!$conn){
    //mysql_error()是返回mysql的上一次操作
    die("连接失败".mysql_error());
}else{
        //echo '连接成功！<br/>';
}
//2、选择对应的数据库
@mysql_select_db("etemsdb");
//3、设置操作编码（可有可无，建议有）
mysql_query("set names utf8");//保证是按照utf8码操作的【utf8无-的，有-的是网页版的！！！！！！！】

$draw = $_GET['draw']; //第几次请求
//排序
$order_column = $_GET['order']['0']['column']; // 哪一列排序
$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 teacher_id ".$order_dir;break;
        case 1;$orderSql = " order by teacher_name ".$order_dir;break;
        // case 2;$orderSql = " order by teacher_password ".$order_dir;break;
        case 2;$orderSql = " order by teacher_email ".$order_dir;break;
        case 3;$orderSql = " order by teacher_date ".$order_dir;break;
        case 4;$orderSql = " order by teacher_note ".$order_dir;break;
        default;$orderSql = '';
    }
}

//分页
$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(teacher_id) as sum FROM teacher";
//条件过滤后记录数 必要
$recordsFiltered = 0;
//表的总记录数 必要
$recordsTotal = 0;
$recordsTotalResult = mysql_query($sumSql,$conn);
while($row=mysql_fetch_assoc($recordsTotalResult)){
    $recordsTotal = $row['sum'];
}
//搜索//定义过滤条件查询过滤后的记录数sql
$search = $_GET['search']['value'];//获取前台传过来的过滤条件
// 每列搜索
$j = 1;
$search_arr =array();
$colSearchSql='';
for($j=0;$j<6;$j++){
    $colSearchable = $_GET['columns'][$j]['searchable'];
    $colSearchVal = $_GET['columns'][$j]['search']['value'];
    if(strlen($colSearchVal)>0 && $colSearchable == 'true' ){
        switch ($j) {
            case 0: $colSearchVal = "teacher_id LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            case 1: $colSearchVal = "teacher_name LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            case 2: $colSearchVal = "teacher_password LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            case 3: $colSearchVal = "teacher_email LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            case 4: $colSearchVal = "teacher_date LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            case 5: $colSearchVal = "teacher_note LIKE '%".$colSearchVal."%'";
                    array_push($search_arr,$colSearchVal);
                    break;
            default:
                # code...
                break;
        }
    }
}
if(empty($search_arr) == false){
    $colSearchSql = " WHERE ".implode(" AND ",$search_arr);
}
// 综合搜索sql
// $zSearchSql =" where teacher_id || teacher_name || teacher_password || teacher_email || teacher_date || teacher_note LIKE '%".$search."%'";
$zSearchSql = " teacher_id LIKE '%".$search."%' OR teacher_name LIKE '%".$search."%' OR teacher_email LIKE '%".$search."%' OR teacher_date LIKE '%".$search."%' OR teacher_note LIKE '%".$search."%'";
// 拼接搜索SQL
$sumSearchSql = '';
if(strlen($colSearchSql)>0 && strlen($search)>0){
    $sumSearchSql = $colSearchSql." AND (".$zSearchSql.")";
}else if(strlen($colSearchSql)>0 && strlen($search)==0){
    $sumSearchSql = $colSearchSql;
}else if(strlen($colSearchSql)==0 && strlen($search)>0){
    $sumSearchSql = " WHERE ".$zSearchSql;
}else{
    $sumSearchSql = '';
}
if(strlen($sumSearchSql)>0){
    $recordsFilteredResult = mysql_query($sumSql.$sumSearchSql);
    while ($row = mysql_fetch_assoc($recordsFilteredResult)) {
        $recordsFiltered =  $row['sum'];
    }
}else{
    $recordsFiltered = $recordsTotal;
}
// query data
$sql='';
$totalResultSql = "SELECT * FROM teacher";
$infos = array();
// 拼接最终SQL
$sql=$totalResultSql.$sumSearchSql.$orderSql.$limitSql;
$dataResult = mysql_query($sql,$conn);
while ($row = mysql_fetch_assoc($dataResult)) {
    // $obj = array($row['teacher_id'], $row['teacher_name'], $row['teacher_password'], $row['teacher_email'], $row['teacher_date'], $row['teacher_note']);
    // array_push($infos,$obj);
    array_push($infos,$row);

}
// return data
echo json_encode(array(
    "draw" => $draw,
    "recordsTotal" =>$recordsTotal,  // necessary
    "recordsFiltered" =>$recordsFiltered, // necessary
    "data" =>$infos // necessary
    ),JSON_UNESCAPED_UNICODE);

删除记录del.php ，增加记录add.php代码

<?php
// 删除记录
$conn=@mysql_connect("localhost","root",""); //数据库
@mysql_select_db("etemsdb"); //数据表
$id=$_GET['id'];
$sql = "DELETE FROM `teacher` WHERE teacher_id = ".$id;
$res = mysql_query($sql,$conn);
if($res){
    echo json_encode(array(
        "status"=>true,
        // "data"=>"this is data~",
        "id"=>$id
    ),JSON_UNESCAPED_UNICODE);
}else{
    echo json_encode(array(
        "status"=>false,
        // "data"=>"this is data~",
        "id"=>$id
    ),JSON_UNESCAPED_UNICODE);
}

<?php
// 增加记录
$conn=@mysql_connect("localhost","root",""); //数据库
@mysql_select_db("etemsdb"); //数据表

$name=$_GET['name'];
$pwd=$_GET['pwd'];
$email=$_GET['email'];
$date=$_GET['date'];
$note=$_GET['note'];

$sql="INSERT INTO teacher (  `teacher_name`, `teacher_password`, `teacher_email`, `teacher_date`, `teacher_note`) VALUES ('".$name."',md5('".$pwd."'),'".$email."','".$date."','".$note."')";
$res=mysql_query($sql,$conn);

if($res){
    echo json_encode(array(
        "status"=>true,
        "info"=>"add success"
        ),JSON_UNESCAPED_UNICODE);
}else{
    echo json_encode(array(
        "status"=>false,
        "info"=>"add error",
        "sql"=>$sql
        ),JSON_UNESCAPED_UNICODE);
}

 

参考文档：　datatables服务器端分页：http://www.qdfuns.com/notes/16811/278151cce7baf16d3c7ed8e830dde70e.html  支持ajax php搜索分页，可见附件

 

　　　　　　中文官网安装使用手册：http://datatables.club/manual/install.html　　　　　　

　　　　　　datatable+bootstrap 表格分页实例 ：http://runjs.cn/detail/ejbscmyt

附件： 链接：http://pan.baidu.com/s/1jIa4Gq6 密码：13ns