php类与API对接的mysql语句
数据表操作
删除（数据记录、数据表）
创建（记录、数据分表）
读取数据记录
导出Excel表格
删除主键
组合筛选条件
时间和文本条件筛选
字符查找函数
IN条件筛选
数据表操作
近期开发不同的工作项目，使用到mysql筛选语句较多。为了方便工作，也是实现“CV”程序猿的快捷操作，现将不同时期的php+mysql条件筛选语句予以整理。
删除（数据记录、数据表）
删除记录；
删除字段；
删除数据表；
case "del"; $tb_id = get_param('tb_id'); $tb_name = get_param('tb_name'); if (is_array($tb_id)) { $tb_id = implode(",", $tb_id); } if ($tb_id == '') { die("tb_id错误"); } //删除记录; $db->delete('subtables', 'tb_id in(' . $tb_id . ')'); //删除数据表; $sql = "DROP TABLE IF EXISTS " . $CONF['db_prefix'] . $tb_name; $row = $db->queryall($sql); //操作日志; addlogs($_COOKIE["adminname"], $tb_id . '数据分表删除，STATUS:OK', '', time(), getip()); break;
创建（记录、数据分表）
- 插入记录；
- 创建数据表分表；
//添加; case "add"; $tb_name = $_POST['tb_name']; $td_times = time(); $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)); } //创建数据； $db->insert('subtables', array('tb_name' => $tb_name, 'td_times' => $td_times)); //创建分表; $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)); //操作日志; addlogs($_COOKIE["adminname"], $tb_name . '数据分表创建，STATUS:OK', '', time(), getip()); break;
读取数据记录
- 读取全部记录；
- 统计记录行数；
- 分页操作；
//读取列表数据; default: $page = get_param('page'); $page = intval($page) == 0 ? 1 : intval($page); $pagenum = 15; $startI = $page * $pagenum - $pagenum; $count = $db->rowcount('subtables', ''); $pages = getPages($count, $page, $pagenum); $row = $db->fetchall('subtables', '*', '', ' tb_id DESC', $startI . ',' . $pagenum);
导出Excel表格
- PHP导出Excel功能
//导出成绩; case "exp"; require 'libs/rescue.export.php'; $sql = "select room_lnglat,room_address,room_depart,room_floor,room_num,res_name,res_mobile,res_persons,res_task,res_status,from_unixtime(submit_time)AS submit_time from " . $db->table($CONF['current_table']) . " where 1"; $sql .= " ORDER BY res_id DESC"; $row = $db->queryall($sql); $name = "码上救援" . date("Y.m.d"); //导出成绩; expExcel($row, $name); //操作日志; addlogs($_COOKIE["adminname"], $name . '数据导出，STATUS:OK', '', time(), getip()); break;
删除主键
- 删除主键；
- 创建新的主键并自动增长；
//删除主键; $sql = "ALTER TABLE " . $db->table('linksdata') . " DROP id"; $row = $db->queryall($sql); //创建新的主键且自增; $sql2 = "ALTER TABLE " . $db->table('linksdata') . " ADD id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"; $row2 = $db->queryall($sql2);
组合筛选条件
- 预置$filter_condition筛选条件；
public function getManageProject() { 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; //设置筛选条件; if ($_COOKIE['dbRoles'] == 2) {//县管理员 $filter_condition = " pro_audit = 2 AND user_area = '" . $_COOKIE['user_area'] . "'"; } if ($_COOKIE['dbRoles'] == 3) {//市管理员 $filter_condition = " pro_audit = 3 AND user_city = '" . $_COOKIE['user_city'] . "'"; } if ($_COOKIE['dbRoles'] == 4) {//省管理员 $filter_condition = " pro_audit = 4 AND user_province = '" . $_COOKIE['user_province'] . "'"; } $sql = "select pro_id,pro_types,pro_name,pro_serial,pro_category,pro_condition,pro_audit,pro_declarant FROM " . $db->table('project') . " WHERE " . $filter_condition; if ($user_city != "") { $sql .= " AND user_city = '" . $user_city . "'"; } if ($user_area != "") { $sql .= " AND user_area = '" . $user_area . "'"; } if ($pro_types != "") { $sql .= " AND pro_types = " . $pro_types; } if ($pro_condition != "") { $sql .= " AND pro_condition = " . $pro_condition; } if ($pro_category != "") { $sql .= " AND pro_category like '%" . $pro_category . "%'"; } if ($pro_name != "") { $sql .= " AND pro_name like '%" . $pro_name . "%'"; } if ($pro_serial != "") { $sql .= " AND pro_serial like '%" . $pro_serial . "%'"; } if ($pro_declarant != "") { $sql .= " AND pro_declarant like '%" . $pro_declarant . "%'"; } $sql .= " ORDER BY pro_id DESC LIMIT " . $limit . "," . $pagesize; $row = $db->queryall($sql);
时间和文本条件筛选
- 时间格式的条件筛选；
- mysql语句时间运算的使用；
public function getEinlass() { global $db, $res; dbc(); $fromTime = get_param("fromTime"); $toTime = get_param("toTime"); $sql = "select fire_id,fire_depart,fire_name,from_unixtime(submit_time,'%Y-%m-%d %H:%i:%s') AS submit_time,TIMESTAMPDIFF(MINUTE, from_unixtime(submit_time,'%Y-%m-%d %H:%i:%s'), NOW()) AS timeDiffer from " . $db->table('fireground') . " where 1"; //按照时间筛选; if ($fromTime != "") { $sql .= " AND DATE_FORMAT(submit_time,'%Y-%m-%d %H:%i%s') >= DATE_FORMAT('$fromTime','%Y-%m-%d %H:%i%s')"; } if ($toTime != "") { $sql .= " AND DATE_FORMAT(submit_time,'%Y-%m-%d %H:%i%s') <= DATE_FORMAT('$toTime','%Y-%m-%d %H:%i%s')"; } $sql .= ' ORDER BY fire_id DESC'; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
字符查找函数
- mysql中instr函数的使用
public function safeDepartAnalysis() { global $db, $res; dbc(); $safeDepart = $_POST['safeDepart']; $selectStatus = $_POST['selectStatus']; $start_date = $_POST['start_date']; $end_date = $_POST['end_date']; //查询语句； $sql = "select safe_id,depart_name,safe_finder,COUNT(safe_finder) AS total from " . $db->table('safe') . " where 1"; //单位查询； if ($safeDepart != "") { $sql .= " AND instr (\" . $safeDepart . \",depart_name) > 0"; } //整改情况查询； if ($selectStatus != "") { $sql .= " AND instr (\" . $selectStatus . \",safe_status) > 0"; } //起始时间； if ($start_date != "") { $sql .= " AND date_format(from_unixtime(safe_time),'%Y-%m-%d') >= '$start_date'"; } //结束时间； if ($end_date != "") { $sql .= " AND date_format(from_unixtime(safe_time),'%Y-%m-%d') <= '$end_date'"; } $sql .= ' GROUP BY depart_name order by safe_id DESC'; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
IN条件筛选
- mysql中in的使用
public function getMakers() { global $db, $res; dbc(); @$poi_category_id = get_param("poi_category_id"); @$keys = $_GET["keys"]; @$poi_type = $_GET["poi_type"]; @$p = $_GET['page'] == "" ? 1 : $_GET['page'];//获取用户选择的页码 @$pagesize = $_GET['limit'] == "" ? 50 : $_GET['limit'];//获取用户选择的每页显示多少条数据 @$limit = ($p - 1) * $pagesize;//偏移量 //获取数据; $sql = "select poi_id,poi_category_id,poi_name,poi_url1,poi_url2,poi_img,poi_lng,poi_lat,poi_status,poi_province,poi_city,poi_county,poi_address,poi_type,poi_video1,poi_video2,poi_contact1,poi_contact2,poi_tel1,poi_tel2 from " . $db->table('poi') . " WHERE 1"; if ($poi_category_id != "") { $sql .= " AND poi_category_id IN ($poi_category_id)"; } //类型; if ($poi_type != "") { $sql .= " AND poi_type = '" . $poi_type . "'"; } //地址或名称; if ($keys != "") { $sql .= " AND (poi_name like '%" . $keys . "%' OR poi_county like '%" . $keys . "%' OR poi_address like '%" . $keys . "%')"; } $sql .= " AND poi_status = 1"; $sql .= " ORDER BY poi_id DESC LIMIT " . $limit . "," . $pagesize; $row = $db->queryall($sql);
@lockdata.cn
