Tracking MySQL Data Changes with PHP

But what if to such a wonderful MySQL opportunity as creating triggers capable of recording old and new data values ​​when inserting, modifying and deleting records add information that the php script has?

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

PHP knows:
* who is currently logged in
* which page is open, which one you have switched from
* brazzer
* IP address
* POST, GET
* Cookie
')
How can you manage to write down all the 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 absolute majority of web server configurations and php).

Thus, if the trigger will also write to the temporary table - we will be able to find out who or what is responsible for specific changes.

A little more detail - we can create a temporary table at the beginning of the page; if the trigger fires, it writes information into a table intended for tracking data, and the IDs that last_insert_id () returns to write to a temporary table. When completing the work, we refer to the temporary table, and if it is not empty, we write everything we want into the change table from what the downloaded PHP page knows.

Next is an implementation option.

1) Let's start with a 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 the current versions of MySQL, it is impossible to know if the temporary table exists by any query. Therefore, it must be created, so that there is no error, when through php we choose values.

We can make fun, and we can do everything straightforward.

As a small trick, a regular and temporary table with the same name can exist in MySQL at the same time. If there is a temporary one, then it will be used. And each time from php, checking if there are any records inside the temp_watch_changes table there will either be empty, or the aids 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 figure out whether the column names can be used dynamically in triggers . Probably, it is simply impossible, but it is not very necessary for us. 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. It creates 3 triggers - on update, insert, delete. Accepts the name of the table, the columns for which to monitor and the key by which this record can be found (maybe several keys).
Can be called like this:

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

4) Now we will make so that after PHP the page fulfills the data from the temporary table was processed.

We use the register_shutdown_function , which allows you to perform any function at the end of the script. On any project there is a file that is always included - there we will 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.