前言
table 是 layui 中使用频率非常高的一个组件,用于在表格中对数据进行一系列动态化的操作,涵盖了日常业务所涉及的大部分需求。
- 模块加载名称:table
- 在实际开发应用中,table的使用牵涉前后端的配合使用,如筛选查询重载功能,翻页功能等。
一、HTML容器
- 查询条件表单
<div class="layui-row"> <form class="layui-form layui-col-md12 x-so"> <div class="demoTable"> <div class="layui-input-inline"> <select name="major_name" id="major_name" lay-filter="major_name" lay-verify="required" lay-search></select> </div> <div class="layui-input-inline"> <select name="sub_name" id="sub_name" lay-filter="sub_name" lay-verify="required" lay-search></select> </div> <div class="layui-input-inline"><input type="text" class="layui-input" placeholder="姓名" id="user_name" autocomplete="off"></div> <div class="layui-input-inline"><input type="text" class="layui-input" placeholder="学号" id="user_ticket" autocomplete="off"></div> <div class="layui-input-inline"> <select name="user_verify" lay-filter="user_verify" id="user_verify"> <option value="">报名状态</option> <option value="0">未报名</option> <option value="1">已报名</option> <option value="2">已审核</option> </select> </div> <div class="layui-input-inline"> <select name="user_pay" lay-filter="user_pay" id="user_pay"> <option value="">缴费状态</option> <option value="0">未缴费</option> <option value="1">已缴费</option> </select> </div> <a class="layui-btn layui-btn-normal" data-type="reload">查询</a> {if $smarty.cookies.admin_menu eq "1"} <a class="layui-btn" onclick="x_admin_show('导入用户信息','?m=User&a=userImport','','')"><i class="layui-icon layui-icon-form"></i>导入用户信息</a> <a class="layui-btn layui-btn-danger" onclick="x_admin_show('导入缴费信息','?m=User&a=userPayImport','','')"><i class="layui-icon layui-icon-form"></i>导入缴费信息</a> <a class="layui-btn layui-btn-normal" onclick="x_admin_show('导入考试信息','?m=User&a=userExamImport','','')"><i class="layui-icon layui-icon-form"></i>导入考试信息</a> {/if} <span class="x-right" style="line-height:40px"></span> </div> </form> </div>
- 表格容器
<table class="layui-hide" id="lockTabel" lay-filter="lockTabel"></table>
二、php后端API数据
1.核心代码
public function getUser() { global $CONF_TABLE, $db, $res; dbc(); @$user_work = get_param("user_work"); @$user_class = get_param("user_class"); @$user_name = get_param("user_name"); @$user_ticket = get_param("user_ticket"); @$user_verify = get_param("user_verify"); @$user_pay = get_param("user_pay"); @$p = get_param('page') == "" ? 1 : get_param('page'); @$pagesize = get_param('limit') == "" ? 15 : get_param('limit'); @$limit = ($p - 1) * $pagesize; //按学院权限设置筛选条件; if ($_COOKIE['admin_menu'] == 1) {//超级管理员 $filter_condition = " 1"; } elseif ($_COOKIE['admin_menu'] == 0) {//学院管理员 $filter_condition = " user_work = '" . $_COOKIE['admin_depart'] . "'"; } else {//禁止查询 $filter_condition = " 1 = 2"; } /*用户分表 By Poleung 2023-02-26*/ $current_table = "user_" . $CONF_TABLE['current_table']; $sql = "select user_id,user_ticket,user_name,user_gender,user_nation,user_card,user_phone,user_work,user_class,user_photo,user_verify,user_pay,user_sms,user_cet FROM " . $db->table($current_table) . " WHERE " . $filter_condition; if ($user_ticket != "") { $sql .= " AND user_ticket like '%" . $user_ticket . "%'"; } if ($user_name != "") { $sql .= " AND user_name like '%" . $user_name . "%'"; } if ($user_work != "") { $sql .= " AND user_work like '%" . $user_work . "%'"; } if ($user_class != "") { $sql .= " AND user_class like '%" . $user_class . "%'"; } if ($user_verify != "") { $sql .= " AND user_verify = " . $user_verify; } if ($user_pay != "") { $sql .= " AND user_pay = " . $user_pay; } $sql .= " ORDER BY user_verify DESC,user_id DESC LIMIT " . $limit . "," . $pagesize; $row = $db->queryall($sql); //获取总记录; $sql_c = "select user_id FROM " . $db->table($current_table) . " WHERE " . $filter_condition; if ($user_ticket != "") { $sql_c .= " AND user_ticket like '%" . $user_ticket . "%'"; } if ($user_name != "") { $sql_c .= " AND user_name like '%" . $user_name . "%'"; } if ($user_work != "") { $sql_c .= " AND user_work like '%" . $user_work . "%'"; } if ($user_class != "") { $sql_c .= " AND user_class like '%" . $user_class . "%'"; } if ($user_verify != "") { $sql_c .= " AND user_verify = " . $user_verify; } if ($user_pay != "") { $sql_c .= " AND user_pay = " . $user_pay; } $sql_c .= " ORDER BY user_verify DESC,user_id DESC"; $row_c = $db->queryall($sql_c); /*信息输出*/ $res['code'] = 0; $res['msg'] = 0; $res['count'] = count($row_c); $res["data"] = $row; die(json_encode_lockdata($res)); }
2.参数过滤
由于前端传递参数使用get方式,便于筛选和查询,但是也存在着SQL注入漏洞,因此使用get_param()
函数对参数进行安全过滤,具体封装参数不做展示,根据自己系统需要增加即可。
@$user_work = get_param("user_work"); @$user_class = get_param("user_class"); @$user_name = get_param("user_name"); @$user_ticket = get_param("user_ticket"); @$user_verify = get_param("user_verify"); @$user_pay = get_param("user_pay"); @$p = get_param('page') == "" ? 1 : get_param('page'); @$pagesize = get_param('limit') == "" ? 15 : get_param('limit');
3.权限判断
后台角色权限分为超级管理员和学院管理员,为了展示对应的数据,做好限设置筛选条件。
//按学院权限设置筛选条件; if ($_COOKIE['admin_menu'] == 1) {//超级管理员 $filter_condition = " 1"; } elseif ($_COOKIE['admin_menu'] == 0) {//学院管理员 $filter_condition = " user_work = '" . $_COOKIE['admin_depart'] . "'"; } else {//禁止查询 $filter_condition = " 1 = 2"; }
4.获取总记录
在使用table模块翻页时,需要传递总页码,因此在后端开发时,单独增加一个数据记录的SQL查询语句。
$res['count'] = count($row_c);
三、table模块渲染
1.加载layui
layui.use(['table', 'layer', 'form'], function () { var $ = layui.jquery,form = layui.form,table = layui.table; //核心代码 });
2.筛选条件select表单加载
为了便于后台筛选操作,在layui中异步调用函数,快速下拉查询;同时设置lay-search属性,支持下拉和输入查询。
//获取学院; getBigCategory(); getSubCategory('', ''); form.on("select(major_name)", function () { var major_id = $("#major_name").val().split(',')[0]; //console.log(major_id); getSubCategory(major_id,''); });
3.表格渲染
核心代码
//表格渲染; table.render({ elem: '#lockTabel' , url: './api/api.php?act=getUser&token=3cab7ce4142608c0f40c785b5ab5ca24' , layout: ['count', 'prev', 'page', 'next', 'limit', 'skip'] , limits: [15,30, 50, 100, 200, 500, 1000] , limit: 15 , toolbar: '#toolbarDemo' ,defaultToolbar: ['filter','exports', 'print'] , even: true , page: true , id: 'searchTable' , cols: [[ {type: 'checkbox',width: 100} , {field: 'user_photo', title: '证件照', align: 'center', templet: function (d) { if(d.user_photo == null || d.user_photo == ""){ return '<img src="upload/nopic.png" alt="" width="50" height="70">'; }else{ return '<img src="'+d.user_photo+'" alt="" width="50" height="70">'; } } } , {field: 'user_ticket', title: '学号', sort: true} , {field: 'user_name', title: '姓名', sort: true} , {field: 'user_work', title: '学院', sort: true} , {field: 'user_class', title: '班级', sort: true} , {field: 'user_nation', title: '民族', sort: true, hide:true} , {field: 'user_gender', title: '性别', sort: true, hide:true} , {field: 'user_phone', title: '手机号码', sort: true,templet: function (d) { return getPhone(d.user_phone)+ "\t"; }} , {field: 'user_cet', title: '证件类型', sort: true, hide:true} , {field: 'user_card', title: '证件号码', sort: true,templet: function (d) { return getIdHide(d.user_card); }} , {field: 'user_verify', title:'报名状态', sort: true, templet: function (d) { switch (d.user_verify) { case "0": return "<span class='layui-btn layui-btn-xs layui-btn-radius layui-btn-primary layui-border-black'>未报名</span>"; break; case "1": return "<span class='layui-btn layui-btn-xs layui-btn-radius layui-btn-primary layui-border-green'>已报名</span>"; break; case "2": return "<span class='layui-btn layui-btn-xs layui-btn-radius layui-btn-primary layui-border-blue'>已审核</span>"; break; } } } , {field: 'user_pay', title:'缴费状态', sort: true, templet: function (d) { switch (d.user_pay) { case "0": return "<span class='layui-btn layui-btn-xs layui-btn-radius layui-btn-primary layui-border-black'>未缴费</span>"; break; case "1": return "<span class='layui-btn layui-btn-xs layui-btn-radius layui-btn-primary layui-border-green'>已缴费</span>"; break; } } } , {field: 'lock', title: '操作', templet: '#checkboxTpl', width:300, align: 'center'} ]] , error: function(res, err){ //如果是异步请求数据方式,res即为你接口返回的信息。 //如果是直接赋值的方式,res即为:{data: [], count: 99} data为当前页数据、count为数据总长度 console.log(res); //得到当前页码 console.log(err); } });
照片显示
为了图片完整的显示,需要添加对应的CSS样式表,并设置为优先。
.layui-table-cell { height: auto; line-height: 20px; } .layui-table-cell img { height: 50%; max-width: 50%; }
证件照字段调用,同时对是否包含照片进行条件判断,防止无图导致的非友好化界面。
{field: 'user_photo', title: '证件照', align: 'center', templet: function (d) { if(d.user_photo == null || d.user_photo == ""){ return '<img src="upload/nopic.png" alt="" width="50" height="70">'; }else{ return '<img src="'+d.user_photo+'" alt="" width="50" height="70">'; } } }
隐私加密
{field: 'user_card', title: '证件号码', sort: true,templet: function (d) { return getIdHide(d.user_card); }}
封装函数
//身份证加密 function getIdHide(idCard) { if (idCard == null) { return ''; } else { const temp = idCard.substr(4, 10) const newIdCard = idCard.replace(temp, '*******') return newIdCard; } }
4.触发事件
事件判断
//触发事件 table.on('toolbar(lockTabel)', function (obj) { var checkStatus = table.checkStatus(obj.config.id); switch (obj.event) { case 'delete': var data = checkStatus.data; var arr = []; for (var i = 0; i < data.length; i++) { arr.push(data[i].user_id) } //删除选中数据; delAll(arr); break; case 'sendSms': var data = checkStatus.data; var arr = []; for (var i = 0; i < data.length; i++) { arr.push(data[i].user_id) } //console.log(arr); //删除选中数据; sendSms(arr); break; case "sendVerify": var data = checkStatus.data; var arr = []; for (var i = 0; i < data.length; i++) { arr.push(data[i].user_id) } sendVerify(arr); break; } });
事件判断
- 删除操作
//删除操作 function delAll(data) { var lock = false; layer.confirm('确认要删除吗?', function (index) { if(!lock) { if (data.length == 0){ layer.msg('未选择用户', {icon: 1, time: 1000}); return false; } lock = true; $.ajax({ type: "get", url: "?m=User&a=userDeal&act=del", async: true, data: { user_id: data }, dataType: "text", success: function (data) { layer.msg(data + '删除成功', {icon: 1, time: 1000}); location.replace(location.href); } }); } }); }
后端处理
case "del"; $user_id = get_param('user_id'); if (is_array($user_id)) { $user_id = implode(",", $user_id); } if ($user_id == '') { die("user_id错误"); } //删除; $db->delete($current_table, 'user_id in (' . $user_id . ')'); //操作日志; addlogs($_COOKIE["admin_name"], '报名信息批量删除,STATUS:OK', '', time(), getip()); break;
- 一键审核
//一键审核; function sendVerify(data) { var lock = false; layer.confirm('确认要审核吗?', function (index) { if(!lock) { if (data.length == 0){ layer.msg('未选择用户', {icon: 1, time: 1000}); return false; } lock = true; $.ajax({ type: "post", url: "?m=User&a=userDeal&act=sendVerify", async: true, data: { user_id: data }, dataType: "json", success: function (res) { if(res.code == 0){ layer.msg(res.msg, {icon: 1, time: 1000}); }else{ layer.msg(res.msg, {icon: 1, time: 1000},function (){ location.replace(location.href); }); } } ,error: function (err){ console.log(err) } }); } }); }
后端处理
//一键审核; case "sendVerify"; $user_id = $_POST['user_id']; //var_dump($user_id); if (is_array($user_id)) { //循环发送信息; for ($i = 0; $i < count($user_id); $i++) { $row = $db->fetch($current_table, 'user_name,user_sms,user_phone', array('user_id' => $user_id[$i]), ' user_id DESC'); $db->update($current_table, array('user_verify' => 2), array('user_id' => $user_id[$i])); } $res['code'] = 1; $res['msg'] = "报名审核成功"; die(json_encode_lockdata($res)); } else { $row = $db->fetch($current_table, 'user_name,user_sms,user_phone', array('user_id' => $user_id), ' user_id DESC'); $db->update($current_table, array('user_verify' => 2), array('user_id' => $user_id)); $res['code'] = 1; $res['msg'] = $row['user_name'] . "报名审核成功"; die(json_encode_lockdata($res)); } break;
- 群发短信
//发送短信; function sendSms(data) { if (data.length == 0){ layer.msg('未选择用户', {icon: 1, time: 1000}); return false; } $.ajax({ type: "post", url: "?m=User&a=userDeal&act=sendSms", async: true, data: { user_id: data }, dataType: "json", success: function (res) { if(res.code == 0){ layer.msg(res.msg, {icon: 1, time: 1000}); }else{ layer.msg(res.msg, {icon: 1, time: 1000},function (){ location.replace(location.href); }); } } ,error: function (err){ console.log(err) } }); return false; }
后端处理
//发送短信; case "sendSms"; $user_id = $_POST['user_id']; //var_dump($user_id); if (is_array($user_id)) { //循环发送信息; for ($i = 0; $i < count($user_id); $i++) { $row = $db->fetch($current_table, 'user_name,user_sms,user_phone', array('user_id' => $user_id[$i]), ' user_id DESC'); if ($row['user_phone'] == NULL || $row['user_phone'] == "") { $res['code'] = 0; $res['msg'] = $row['user_name'] . "手机号码有误,无法发送"; die(json_encode_lockdata($res)); } //发送短信 TODO //更新发送短信次数 $user_sms = $row['user_sms'] + 1; $db->update($current_table, array('user_sms' => $user_sms), array('user_id' => $user_id[$i])); } $res['code'] = 1; $res['msg'] = "短信发送成功"; die(json_encode_lockdata($res)); } else { $row = $db->fetch($current_table, 'user_name,user_sms,user_phone', array('user_id' => $user_id), ' user_id DESC'); if ($row['user_phone'] == NULL || $row['user_phone'] == "") { $res['code'] = 0; $res['msg'] = $row['user_name'] . "手机号码有误,无法发送"; die(json_encode_lockdata($res)); } //发送短信 TODO //更新发送短信次数 $user_sms = $row['user_sms'] + 1; $db->update($current_table, array('user_sms' => $user_sms), array('user_id' => $user_id)); $res['code'] = 1; $res['msg'] = $row['user_name'] . ",短信发送成功"; die(json_encode_lockdata($res)); } break;
5.执行重载
在筛选框中对应的表单,将相应的值获取,传递给API,进行筛选后查询。
//执行重载; var $ = layui.$, active = { reload: function () { table.reload('searchTable', { page: {curr: 1} , where: { user_work: $("#major_name").val().split(',')[1], user_class: $("#sub_name").val(), user_verify: $('#user_verify').val(), user_pay: $('#user_pay').val(), user_name: $('#user_name').val(), user_ticket: $('#user_ticket').val() } }); } }; $('.demoTable .layui-btn').on('click', function () { var type = $(this).data('type'); active[type] ? active[type].call(this) : ''; });
6.操作按钮
使用table模块中id="checkboxTpl"和id="toolbarDemo"对应的位置调用按钮即可。
<script type="text/html" id="checkboxTpl"> {if $smarty.cookies.admin_menu eq "1"} {literal}<a class="layui-btn layui-btn-xs layui-btn-normal" href="javascript:;" onclick="sendSms('{{d.user_id}}')"><i class="layui-icon layui-icon-cellphone"></i> 短信({{d.user_sms}})</a>{/literal} {/if} {literal} <a class="layui-btn layui-btn-xs" href="index.php?m=Index&a=indexDeal&act=inspect&user_id={{d.user_id}}" target="_blank">准考证</a> <a class="layui-btn layui-btn-xs" href="javascript:;" onclick="x_admin_show('审核','?m=User&a=userEdit&act=show&user_id={{d.user_id}}','','')">审核</a> <a class="layui-btn layui-btn-xs layui-btn-warm" onclick="x_admin_show('修改密码','?m=User&a=userPass&act=pass&user_id={{d.user_id}}','','')" title="修改密码" href="javascript:;">密码</a> {/literal} </script>
<script type="text/html" id="toolbarDemo"> <button class="layui-btn layui-btn-sm layui-btn-danger" lay-event="delete"><i class="layui-icon layui-icon-delete"></i>批量删除</button> <button class="layui-btn layui-btn-sm" lay-event="sendVerify"><i class="layui-icon layui-icon-auz"></i> 报名审核</button> <button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="sendSms"><i class="layui-icon layui-icon-cellphone"></i> 发送短信</button> </script>
总结
layui-table模块涉及:
复选框事件:点击复选框时触发,回调函数返回一个 object 参数;
单元格工具事件:比如编辑、删除,这时可借助单元格工具事件(tool)来实现;
头部工具栏事件:点击头部工具栏区域设定了属性为 lay-event=“” 的元素时触发;
导出任意数据: table 的工具栏内置了数据导出按钮,但有时你可能需要通过方法去导出任意数据,那么可以借助以下方法:table.exportFile(id, data, obj);
自动化渲染的重载;
@漏刻有时