Track data changes in MySQL using PHP

But what if MySQL has such a great feature as creating triggers that can record old and new data values ​​when inserting, changing, and deleting records, adding the information that the php script has?

MySQL triggers know:
* the moment at which the change occurs
* the old and the new value

PHP knows:
* who is currently logged in
* which page is open, which one has gone from
* bravzer
* IP address
* POST, GET
* Cookie

How can you manage to record the whole necessary information?

Take advantage of the features of PHP and MySQL!

MySQL can create temporary tables that live only until the connection to the database is closed, and PHP, just at the opening of each page, creates a new connection (in the vast majority of web server and php configurations).

Thus, if the trigger also writes to the temporary table, we can find out who or what is responsible for specific changes.

In a bit more detail - we can create a temporary table at the beginning of the page, if the trigger fires, it writes information to the table intended for tracking data, and write the identifiers returned by last_insert_id () to the temporary table. At the end of the work, we turn to the temporary table, and if it is not empty, we record in the change table all that we want from what the loaded PHP page knows.

Next is an implementation option.

1) Let's start with the temporary table - we can create it when the trigger is executed on the page for the first time! To do this, just write CREATE TEMPORARY TABLE temp_watch_changes IF NOT EXISTS. There is only a small problem - in current versions of MySQL it is impossible to find out if a temporary table exists by any query. Therefore, it must be created so that there is no error when we select values ​​through php.

We can gimmick, but we can do everything in a straightforward manner.

As a small trick - in MySQL, a regular and temporary table with the same name can exist at the same time. If there is a temporary one, then it will be used. And each time from the php checking whether there are records inside the temp_watch_changes table there will be either empty, or identifiers recorded by the trigger and no errors.

A more straightforward method is to simply create a temporary table each time the page loads. On our server, it takes 0.0008 seconds, which is acceptable in principle :)

CREATE TEMPORARY TABLE temp_watch_changes ( id_change INTEGER NOT NULL )

2) Create a table containing the changes themselves

CREATE TABLE `watch_changes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(255) DEFAULT NULL,
  `column_name` varchar(255) DEFAULT '',
  `key_name` varchar(255) DEFAULT NULL,
  `key_value` varchar(1000) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `type` enum('insert','update','delete') DEFAULT 'update',
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(255) DEFAULT NULL,
  `id_user` int(11) DEFAULT '0',
  `user_email` varchar(255) DEFAULT '',
  `post` text,
  `get` text,
  `session` text,
  `server` text,
  `user_agent` varchar(1000) DEFAULT '',
  `url` text,
  `referer` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3) Create a trigger. It was not possible to find out whether column names can be dynamically used in triggers . Perhaps it’s just not possible, but we don’t really need it. After all, there is PHP.

function createWatchTrigger($table,$columns,$primaryKey){
		if(!is_array($primaryKey)){
			$primaryKey=array($primaryKey);
		}
		$types=array('update','insert','delete');
		foreach($types as $type){
			db::$used->internalQuery("drop trigger IF EXISTS {$table}_t_$type");
			$triggerContent="CREATE TRIGGER {$table}_t_$type
				AFTER $type ON {$table}
				FOR EACH ROW
				BEGIN
					CREATE TEMPORARY TABLE IF NOT EXISTS temp_watch_changes (
						id_change			INTEGER NOT  NULL
					);
				";
			foreach($columns as $columnTitle){
				if($type=='update'){
					$triggerContent.="
					IF NEW.{$columnTitle} != OLD.$columnTitle
					THEN ";
				}
				$triggerContent.="INSERT INTO watch_changes (table_name,column_name,old_value,new_value,type,key_name,key_value) ";
				if($type=='insert'){
					$triggerContent.="VALUES('{$table}','$columnTitle','', NEW.$columnTitle,'$type','".implode(',',$primaryKey)."',CONCAT('',NEW.".implode(",',',NEW.",$primaryKey)."));";
				}else if($type=='update'){
					$triggerContent.="VALUES('{$table}','$columnTitle',OLD.$columnTitle, NEW.$columnTitle,'$type','".implode(',',$primaryKey)."',CONCAT('',NEW.".implode(",',',NEW.",$primaryKey)."));";
				}else if($type=='delete'){
					$triggerContent.="VALUES('{$table}','$columnTitle',OLD.$columnTitle,'','$type','".implode(',',$primaryKey)."',CONCAT('',OLD.".implode(",',',OLD.",$primaryKey)."));";
				}
				$triggerContent.="
				set @last_id=last_insert_id();
				INSERT INTO temp_watch_changes (id_change) values (@last_id);";
				if($type=='update'){
					$triggerContent.="END IF;";
				}
			}
			$triggerContent.="\nEND;";
			db::$used->internalQuery($triggerContent);
		}
	}

The creation function itself could be better in terms of readability. She creates 3 triggers - on update, insert, delete. It takes the name of the table, the columns for which to monitor and the key by which to find this record (there may be several keys).
It can be called like this:

createWatchTrigger('employees',array('salary','job_title'),'id');

4) Now we will make it so that after the PHP page has finished processing the data from the temporary table is processed.

We will use register_shutdown_function , which allows you to execute any function upon completion of the script. On any project there is a file that is always included - we’ll place it there.

function shutdown(){
	$affectedRows=db::$used->fetchRows("select * from temp_watch_changes");
	if($affectedRows){
		if(User::isLogged()){
			$userId=User::getCurrent()->getId();
			$email=User::getCurrent()->getEmail();
		}else{
			$userId=0;
			$email='';
		}
		$updateData=array(
			'ip'=>$_SERVER['REMOTE_ADDR'],
			'id_user'=>$userId,
			'user_email'=>$email,
			'post'=>serialize($_POST),
			'get'=>serialize($_GET),
			'session'=>serialize($_SESSION),
			'server'=>serialize($_SERVER),
			'user_agent'=>$_SERVER['HTTP_USER_AGENT'],
			'url'=>serialize($_SERVER['REQUEST_URI']),
			'referer'=>$_SERVER['HTTP_REFERER']
		);
		foreach($affectedRows as $row){
			db::$used->update('watch_changes',$updateData,array('id'=>$row['id_change']));
		}
	}
}
register_shutdown_function('shutdown');

That's all.