Thursday, March 21, 2013

MySQL transaction log in PHP

We may need to create a log table for recording  MySQL transaction in PHP.
 Below is  the log table edu_admin_log
CREATE TABLE  edu_admin_log (
  `login` varchar(30) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `operation` varchar(20) NOT NULL default '',
  `table` varchar(30) NOT NULL default '',
  `num_affected` tinyint(3) NOT NULL default '0',
  `details` text NOT NULL,
  KEY `login` (`login`)

Here 'details' is sql query. Operation is 1,2,3,4 or 5, corresponding to  update,  delete, insert, replace and other action.

Create write_to_log function  to record MySQL transaction:
function write_to_log($operation_type, $table_name, $num_affected, $details) {
    global $db;
    static $now;

    if (!$now) {
        $now = date('Y-m-d H:i:s');
    if ($num_affected > 0) {
        $details = addslashes(stripslashes($details));
        $sql    = "INSERT INTO edu_admin_log VALUES ('$_SESSION[login]', '$now', '$operation_type', '$table_name', $num_affected, '$details')";
        $result = mysql_query($sql, $db);

    $sql    = "DELETE FROM edu_member_track WHERE member_id=$id";
    mysql_query($sql, $db);
    write_to_log(2, 'member_track', mysql_affected_rows($db), $sql);

No comments:

Post a Comment