浅谈管理系统操作日志设计（附操作日志类）

  1 class LOG{
  2     protected $primaryid;
  3     protected $tbid;
  4     protected $tbname;
  5     protected $keys;
  6     protected $values;
  7     /**
  8      * 参数说明
  9      * int              $tbid       查询指定表的id
 10      * string           $tbname     数据库表名
 11      */
 12     public function insert($tbid,$tbname){
 13         global $db;
 14         //查询表注释
 15         $db->query(‘show table status where name = "‘.$tbname.‘"‘);
 16         $tb = $db->fetch();
 17         //插入日志主表
 18         $returnid = $db->insert(0,2,‘tb_log‘,array(
 19             ‘adminid = ‘.$_SESSION[‘admin‘][‘id‘], 20             ‘type = 1‘, 21             ‘tableid = ‘.$tbid, 22             ‘tablename = "‘.$tbname.‘"‘, 23             ‘comment = "‘.$tb[‘Comment‘].‘"‘, 24             ‘dt = now()‘
 25         ));
 26         //查询字段注释
 27         $db->query(‘show full columns from ‘.$tbname);
 28         $tb = $db->fetchAll();
 29         foreach($tb as $v){
 30             $commentArray[$v[‘Field‘]] = $v[‘Comment‘];
 31         }
 32         //查询所有字段信息，插入日志从表
 33         $rs = $db->select(0,1,$tbname,‘*‘,‘and tbid = ‘.$tbid);
 34         $keys = array_keys($rs);
 35         $values = array_values($rs);
 36         for($i = 0; $i < count($keys); $i++){
 37             $db->insert(0,‘tb_log_content‘,array(
 38                 ‘logid = ‘.$returnid, 39                 ‘tbkey = "‘.$keys[$i].‘"‘, 40                 ‘tbvalue = "‘.$values[$i].‘"‘, 41                 ‘comment = "‘.$commentArray[$keys[$i]].‘"‘
 42             ));
 43         }
 44     }
 45     public function updateStart($tbid,$tbname){
 46         global $db;
 47         //查询表注释
 48         $db->query(‘show table status where name = "‘.$tbname.‘"‘);
 49         $tb = $db->fetch();
 50         //插入日志主表
 51         $returnid = $db->insert(0,array(
 52             ‘adminid = ‘.$_SESSION[‘admin‘][‘id‘], 53             ‘type = 2‘, 54             ‘tableid = ‘.$tbid, 55             ‘tablename = "‘.$tbname.‘"‘, 56             ‘comment = "‘.$tb[‘Comment‘].‘"‘, 57             ‘dt = now()‘
 58         ));
 59         //查询修改前数据信息
 60         $rs = $db->select(0,‘and tbid = ‘.$tbid);
 61         $keys = array_keys($rs);
 62         $values = array_values($rs);
 63         $this->primaryid = $returnid;
 64         $this->tbid = $tbid;
 65         $this->tbname = $tbname;
 66         $this->keys = $keys;
 67         $this->values = $values;
 68     }
 69     public function updateEnd(){
 70         global $db;
 71         //查询字段注释
 72         $db->query(‘show full columns from ‘.$this->tbname);
 73         $tb = $db->fetchAll();
 74         foreach($tb as $v){
 75             $commentArray[$v[‘Field‘]] = $v[‘Comment‘];
 76         }
 77         //查询修改后数据信息
 78         $rs = $db->select(0,$this->tbname,‘and tbid = ‘.$this->tbid);
 79         $currentvalues = array_values($rs);
 80         //前后信息进行比较
 81         for($i = 0; $i < count($currentvalues); $i++){
 82             if($this->values[$i] !== $currentvalues[$i]){
 83                 $db->insert(0,array(
 84                     ‘logid = ‘.$this->primaryid, 85                     ‘tbkey = "‘.$this->keys[$i].‘"‘, 86                     ‘tbvalue = "‘.$this->values[$i].‘"‘, 87                     ‘currenttbvalue = "‘.$currentvalues[$i].‘"‘, 88                     ‘comment = "‘.$commentArray[$this->keys[$i]].‘"‘
 89                 ));
 90             }
 91         }
 92     }
 93     public function delete($tbid,$tbname){
 94         global $db;
 95         //查询表注释
 96         $db->query(‘show table status where name = "‘.$tbname.‘"‘);
 97         $tb = $db->fetch();
 98         //插入日志主表
 99         $returnid = $db->insert(0,array(
100             ‘adminid = ‘.$_SESSION[‘admin‘][‘id‘],101             ‘type = 3‘,102             ‘tableid = ‘.$tbid,103             ‘tablename = "‘.$tbname.‘"‘,104             ‘comment = "‘.$tb[‘Comment‘].‘"‘,105             ‘dt = now()‘
106         ));
107         //查询字段注释
108         $db->query(‘show full columns from ‘.$tbname);
109         $tb = $db->fetchAll();
110         foreach($tb as $v){
111             $commentArray[$v[‘Field‘]] = $v[‘Comment‘];
112         }
113         //查询所有字段信息，插入日志从表
114         $rs = $db->select(0,‘and tbid = ‘.$tbid);
115         $keys = array_keys($rs);
116         $values = array_values($rs);
117         for($i = 0; $i < count($keys); $i++){
118             $db->insert(0,array(
119                 ‘logid = ‘.$returnid,120                 ‘tbkey = "‘.$keys[$i].‘"‘,121                 ‘tbvalue = "‘.$values[$i].‘"‘,122                 ‘comment = "‘.$commentArray[$keys[$i]].‘"‘
123             ));
124         }
125     }
126 }

 1 -- ----------------------------
 2 -- Table structure for `tb_log`
 3 -- ----------------------------
 4 CREATE TABLE `tb_log` (
 5   `tbid` bigint(20) NOT NULL AUTO_INCREMENT, 6   `adminid` bigint(20) DEFAULT NULL COMMENT ‘管理员id‘, 7   `type` tinyint(4) DEFAULT ‘1‘ COMMENT ‘操作类型：1新增2修改3删除‘, 8   `tableid` bigint(20) DEFAULT NULL, 9   `tablename` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘表名‘,10   `comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,11   `dt` datetime DEFAULT NULL,12   PRIMARY KEY (`tbid`)
13 ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
14  
15 -- ----------------------------
16 -- Table structure for `tb_log_content`
17 -- ----------------------------
18 CREATE TABLE `tb_log_content` (
19   `tbid` bigint(20) NOT NULL AUTO_INCREMENT,20   `logid` bigint(20) DEFAULT NULL,21   `tbkey` longtext COLLATE utf8_unicode_ci,22   `tbvalue` longtext COLLATE utf8_unicode_ci,23   `currenttbvalue` longtext COLLATE utf8_unicode_ci,24   `comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,25   PRIMARY KEY (`tbid`)
26 ) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;