项目拓展性的重要标准就是API接口，根据项目的需要，实时开发对应API接口来延展项目的需求。本案例是多个项目开发经验的总结，便于后期复盘，汲取经验。
1.跨表查询API
下面API接口是一个综合性极强的API接口，涉及
跨表查询功能；
关键词筛选功能；
layui后台动态翻页功能；
public function getExpertMyProject() { global $db, $res; dbc(); @$user_city = get_param("user_city"); @$user_area = get_param("user_area"); @$pro_category = get_param("pro_category"); @$pro_name = get_param("pro_name"); @$pro_serial = get_param("pro_serial"); @$pro_declarant = get_param("pro_declarant"); @$pro_condition = $_GET["pro_condition"]; @$pro_types = $_GET["pro_types"]; @$p = $_GET['page'] == "" ? 1 : $_GET['page']; @$pagesize = $_GET['limit'] == "" ? 15 : $_GET['limit']; @$limit = ($p - 1) * $pagesize; $sql = "select a.pro_id,a.user_id,MAX(a.ex_times) as ex_times,b.pro_id,b.pro_types,b.pro_audit,b.pro_name,b.pro_serial,b.pro_category,b.pro_condition,b.pro_declarant FROM " . $db->table('expert') . " AS a, " . $db->table('project') . " AS b WHERE a.user_id =" . $_COOKIE['user_id'] . " AND a.pro_id = b.pro_id "; if ($user_city != "") { $sql .= " AND a.user_city = '" . $user_city . "'"; } if ($user_area != "") { $sql .= " AND a.user_city = b.user_city AND a.user_area = '" . $user_area . "'"; } if ($pro_types != "") { $sql .= " AND b.pro_types = " . $pro_types; } if ($pro_condition != "") { $sql .= " AND b.pro_condition = " . $pro_condition; } if ($pro_category != "") { $sql .= " AND b.pro_category like '%" . $pro_category . "%'"; } if ($pro_name != "") { $sql .= " AND b.pro_name like '%" . $pro_name . "%'"; } if ($pro_serial != "") { $sql .= " AND b.pro_serial like '%" . $pro_serial . "%'"; } if ($pro_declarant != "") { $sql .= " AND b.pro_declarant like '%" . $pro_declarant . "%'"; } $sql .= " GROUP BY a.pro_id ORDER BY a.ex_id DESC LIMIT " . $limit . "," . $pagesize; $row = $db->queryall($sql); //获取总记录; $sql_c = "select a.pro_id,MAX(a.ex_times) AS ex_times,b.pro_id FROM " . $db->table('expert') . " AS a," . $db->table('project') . " AS b WHERE a.pro_id = b.pro_id";// AND b.pro_status = 1 //设置筛选条件; if ($_COOKIE['dbRoles'] == 2) {//县管理员 $sql_c .= " AND a.user_city = b.user_city AND a.user_area = b.user_area"; } if ($_COOKIE['dbRoles'] == 3) {//市管理员 $sql_c .= " AND a.user_city = b.user_city"; } if ($_COOKIE['dbRoles'] == 4) {//省管理员 $sql_c .= " AND a.user_province = b.user_province"; } if ($pro_types != "") { $sql_c .= " AND b.pro_types = " . $pro_types; } if ($pro_condition != "") { $sql_c .= " AND b.pro_condition = " . $pro_condition; } if ($user_city != "") { $sql_c .= " AND a.user_city = '" . $user_city . "'"; } if ($user_area != "") { $sql_c .= " AND a.user_area = '" . $user_area . "'"; } if ($pro_category != "") { $sql_c .= " AND b.pro_category like '%" . $pro_category . "%'"; } if ($pro_name != "") { $sql_c .= " AND b.pro_name like '%" . $pro_name . "%'"; } if ($pro_serial != "") { $sql_c .= " AND b.pro_serial like '%" . $pro_serial . "%'"; } if ($pro_declarant != "") { $sql_c .= " AND b.pro_declarant like '%" . $pro_declarant . "%'"; } $sql_c .= " GROUP BY a.pro_id ORDER BY a.ex_id DESC"; $row_c = $db->queryall($sql_c); /*信息输出*/ $res['code'] = 0; $res['msg'] = "OK"; $res['count'] = count($row_c); $res["data"] = $row; die(json_encode_lockdata($res)); }
2.地图下钻API接口
传递不同的地区参数，获取对应的区域数据。例如：省级–地市级，二级下钻。默认读取省级数据，当传递地级市名称的时候，获取区县级数据。
public function getProjectMap() { global $db, $res; dbc(); @$user_city = get_param("user_city"); if ($user_city != "") { $sql = "select user_city,user_area,COUNT(CASE pro_status WHEN '2' THEN 2 END) AS total FROM " . $db->table('project') . " WHERE 1"; $sql .= " AND user_city ='" . $user_city . "'"; $sql .= " GROUP BY user_area"; } else { $sql = "select user_city,COUNT(CASE pro_status WHEN '2' THEN 2 END) AS total FROM " . $db->table('project') . " WHERE 1"; $sql .= " GROUP BY user_city"; } $row = $db->queryall($sql); $res['msg'] = "OK"; $res["data"] = $row; die(json_encode_lockdata($res)); }
3.循环读取API
//总站-城市站点排名信息表; public function getCityInfo() { global $db, $res; dbc(); @$p = $_GET['page'] == "" ? 1 : $_GET['page'];//获取用户选择的页码 @$pagesize = $_GET['limit'] == "" ? 10 : $_GET['limit'];//获取用户选择的每页显示多少条数据 @$keys = $_GET['keys'];//站点Id @$fromTime = $_GET['fromTime'];//开始时间 @$toTime = $_GET['toTime'];//结束时间 @$limit = ($p - 1) * $pagesize;//偏移量 //筛选全部记录并随前端自动分页展示; $sql = "select a.uniacid,a.name,a.dpopen,a.short_name from " . $db->table('set_basic') . " AS a where a.dpopen = 1"; //按照站点筛选; IF ($keys != "") { $sql .= " AND a.uniacid = " . $keys; } $sql .= " ORDER BY a.uniacid DESC"; $sql .= " LIMIT " . $limit . "," . $pagesize; $row = $db->queryall($sql); //获取总记录数量; $sql_c = "select a.uniacid,a.name,a.dpopen,a.short_name from " . $db->table('set_basic') . " AS a where a.dpopen = 1"; //按照站点筛选; IF ($keys != "") { $sql_c .= " AND a.uniacid = " . $keys; } $sql_c .= " ORDER BY a.uniacid DESC"; $row_c = $db->queryall($sql_c); $count = count($row_c); //获取对应站点的销量及对应的item; foreach ($row as $k => $v) { $sql_p = "select COUNT(a.id) AS transTotal,SUM(a.should_paid) AS totalPays,COUNT(DISTINCT a.user_id) AS totalUser,a.paid_at,a.uniacid,SUM(a.all_num) AS totalAmount from " . $db->table('orders') . " AS a"; $sql_p .= " WHERE a.status <> '-128' AND a.uniacid =" . $v['uniacid'];//AND a.status <> '-126' //按照时间筛选; IF ($fromTime != "") { $sql_p .= " AND date_format(a.paid_at,'%Y-%m-%d') >= '$fromTime'"; } IF ($toTime != "") { $sql_p .= " AND date_format(a.paid_at,'%Y-%m-%d') <= '$toTime'"; } $sql_p .= " GROUP BY a.uniacid"; $row[$k]["products"] = $db->queryall($sql_p); } //获取对应站点团长的数量; foreach ($row as $k => $v) { $sql_l = "select IFNULL(COUNT(id),0) AS leaderTotal from " . $db->table('leader'); $sql_l .= " WHERE created_at <> '' AND uniacid =" . $v['uniacid'];//AND server = 1 AND legitimate = 1 //按照时间筛选; IF ($fromTime != "") { $sql_l .= " AND date_format(created_at,'%Y-%m-%d') >= '$fromTime'"; } IF ($toTime != "") { $sql_l .= " AND date_format(created_at,'%Y-%m-%d') <= '$toTime'"; } $sql_l .= " GROUP BY uniacid"; $row[$k]["leader"] = $db->queryall($sql_l); } //获取对应的站点会员数量; foreach ($row as $k => $v) { $sql_m = "select COUNT(id) AS memberTotal from " . $db->table('member'); $sql_m .= " WHERE created_at <> '' AND legitimate = 1 AND uniacid =" . $v['uniacid']; //按照时间筛选; IF ($fromTime != "") { $sql_m .= " AND date_format(created_at,'%Y-%m-%d') >= '$fromTime'"; } IF ($toTime != "") { $sql_m .= " AND date_format(created_at,'%Y-%m-%d') <= '$toTime'"; } $sql_m .= " GROUP BY uniacid"; $row[$k]["member"] = $db->queryall($sql_m); } $res['code'] = 0; $res['count'] = $count; $res['msg'] = ""; $res["data"] = $row; die(json_encode_lockdata($res)); }
4.日期条件筛选判断API
//会员排名; public function getMemberRank() { global $db, $res; dbc(); @$CR_days = $_POST['days']; @$limitNum = $_POST['limitNum']; if ($limitNum == "") { $limitNum = "10"; } $sql = "select SUM(a.should_paid) AS totalPays,a.user_id,b.id,b.nickname as name from " . $db->table('orders'); $sql .= " as a, " . $db->table('member') . " as b where b.id= a.user_id AND a.paid_at <> ''"; switch ($CR_days) { case -1; $sql .= " AND TO_DAYS(CURDATE()) - TO_DAYS(date_format(a.paid_at,'%Y-%m-%d')) = 1"; break; case 0; $sql .= " AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE()"; break; case 7; $sql .= " AND DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(a.paid_at)"; break; case 30; $sql .= " AND DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= date(a.paid_at)"; break; case 2;//本月，修改 BY jieji007,2020-08-31 $sql .= " AND date_format(a.paid_at,'%Y-%m') = date_format(CURDATE(),'%Y-%m')"; break; case 3;//上月，修改 BY jieji007,2020-08-31 $sql .= " AND date_format(a.paid_at,'%Y-%m') = date_format(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')"; break; case "100";//全部信息; break; default: $sql .= " AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE()"; } if (@$_COOKIE['Controller'] == "-1") { $sql .= " AND a.uniacid = " . $_COOKIE['uniacid']; } $sql .= " GROUP BY a.user_id ORDER BY totalPays DESC"; $sql .= " LIMIT " . $limitNum; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
5.数据更新API
通过该API实现对数据库数据进行增改删的动作。
public function updateUser() { global $db, $res; dbc(); @$user_id = get_param("user_id"); @$user_name = get_param("user_name"); @$user_phone = get_param("user_phone"); @$user_roles = get_param("user_roles"); @$user_surveyor_type = get_param("user_surveyor_type"); @$user_expert_type = get_param("user_expert_type"); @$user_manage_type = get_param("user_manage_type"); @$user_source = get_param("user_source"); $db->update('user', array('user_name' => $user_name, 'user_phone' => $user_phone, 'user_roles' => $user_roles, 'user_surveyor_type' => $user_surveyor_type, 'user_expert_type' => $user_expert_type, 'user_manage_type' => $user_manage_type, 'user_source' => $user_source), array("user_id" => $user_id)); $res['msg'] = "OK"; $res["data"] = $user_id . "-信息更新成功."; die(json_encode_lockdata($res)); }
6.创建数据库分表API：
当数据超过一定的数据量时，需要对mysql数据库进行分库分表。
判断数据表名，是否重复
$row = $db->fetch('subtables', '*', array('tb_name' => trim(addslashes($tb_name))), ' tb_id DESC'); if ($row) { $res['code'] = 0; $res['msg'] = "数据表名已存在，请更换名称。"; die(json_encode_lockdata($res)); }
创建新的数据表
//创建分表; $sql = "CREATE TABLE IF NOT EXISTS `" . $CONF['db_prefix'] . $tb_name . "` ( `res_id` int(10) NOT NULL COMMENT 'id', `room_id` int(11) DEFAULT NULL COMMENT '房间号', `data_id` varchar(64) DEFAULT NULL COMMENT '同步id', `room_lnglat` varchar(255) DEFAULT NULL, `room_address` varchar(255) DEFAULT NULL, `room_depart` varchar(255) DEFAULT NULL, `room_floor` varchar(64) DEFAULT NULL COMMENT '楼层', `room_num` varchar(255) DEFAULT NULL COMMENT '房号', `res_name` varchar(255) DEFAULT NULL COMMENT '姓名', `res_mobile` varchar(32) DEFAULT NULL COMMENT '手机', `res_persons` int(2) DEFAULT NULL COMMENT '被困人数', `res_task` varchar(255) DEFAULT NULL COMMENT '任务领取', `res_status` varchar(255) DEFAULT NULL COMMENT '救援情况', `user_name` varchar(10) DEFAULT NULL COMMENT '提交人', `submit_time` int(11) DEFAULT NULL COMMENT '提交时间', `update_time` int(11) DEFAULT NULL COMMENT '更新时间' ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='码上救援';"; $row = $db->queryall($sql); $res['code'] = 1; $res['msg'] = "数据分表创建成功，请在conf/config中配置切换"; die(json_encode_lockdata($res));
@lockdata.cn
