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

  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, 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, 2, 'tb_log', 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, 1, $tbname, '*', '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, 1, $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, 0, 'tb_log_content', 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, 2, 'tb_log', 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, 1, $tbname, '*', '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, 0, 'tb_log_content', 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;

原文章地址：https://www.cnblogs.com/hooray/archive/2012/09/05/2672133.html