php类与API对接的mysql语句
数据表操作
近期开发不同的工作项目，使用到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
