漏刻有时操作数据php类记录及筛选条件的写法

数据表操作

近期开发不同的工作项目，使用到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')