The audit log tells you who changed every value on every item in your system. It tells you when they did it and what page they were on when they did it.

This is used all the time in my daily work. We can see who change any record in the system, what page they made those changes on, and what other values were changed at the same time. We can see a complete history of changes made, and that’s incredibly helpful when you need to figure out, “What Happened?!?”

function writeAuditLog($params) {
	$databaseName = $params['databaseName'];
	$tableName = $params['tableName'];
	$primaryKeyName = $params['primaryKeyName'];
	$primaryKeyValue = $params['primaryKeyValue'];
	$newDataArray = $params['newDataArray'];
	$arbitraryValue = $params['arbitraryValue'];
	
	$id_user = $_SESSION['session_id_user'];
	$page = curPageURL();
	
	if($arbitraryValue!='' || (is_array($newDataArray) && count($newDataArray)>0)) {
		
		$performQueries = true;
		mysql_query("SET NAMES utf8",$GLOBALS['standardDBlink']);
		$query = "UPDATE audit_log SET database_name='".encodeSQL($databaseName)."',
										table_name='".encodeSQL($tableName)."',
										primary_key_value='".encodeSQL($primaryKeyValue)."',
										page_performed_on='".encodeSQL($page)."'";
		if($id_user!='') {
			$query .= ", id_contact_performing_change='".encodeSQL($id_user)."' ";
		}
		if($arbitraryValue!='') {
			$query .= ", change_log='".encodeSQL($arbitraryValue)."'";
		} elseif(is_array($newDataArray) && count($newDataArray)>0) {
			$change_log = '';
			$change_arry = array();
			$subQuery = "SELECT * FROM ".$databaseName.".".$tableName." 
							WHERE `".$primaryKeyName."`='".$primaryKeyValue."'";
			$data = getMySQLData($subQuery, false, true);
			if($data) {
				foreach($newDataArray AS $key=>$new_value) {
					$old_value = mysql_result($data,0,$key);
					if($old_value!=$new_value) {
						$change_log .= $key.": ".$old_value." [ changed to ] ".$new_value."\n";
						$change_arry[$key] = array(
							'orig' => (string)$old_value,
							'changed_to' => (string)$new_value
							);
					}
				}
			}
			if(count($change_arry) > 0) {
				$change_log = serialize($change_arry);
			}
			if($change_log == "") {
				$performQueries = false;
			}
			$query .= ", change_log='".encodeSQL($change_log)."'";
		}
		

		if($performQueries) {
			$insertQuery = "INSERT INTO audit_log (`datetime_created`) VALUES (NOW())";
			$id = insertMySQLData($insertQuery);
			$query .= " WHERE id='".$id."'";
			updateMySQLData($query);
		}
	}
}

August 20th, 2013

Posted In: Functions

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: