正文
基于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
