History of Field changes - Logging how??

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

History of Field changes - Logging how??

Post by toconnell » 2013-04-24 14:55

I would like to get a historical record when a user makes a change to certain tables, I would like it to log the old content and the new content, the date, time, user id, field name changed and record id changed. How can I do this? Any sugestions?
Feel free to email me: [email protected]
Tina O'Connell
Web Dev & Appgini FAN

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: History of Field changes - Logging how??

Post by peebee » 2013-04-25 02:33

An adaptation of a hook example already supplied by Ahmad (the AppGini developer) looks like it might do the trick for you.

Look here at the example under "tablename_after_delete()": http://bigprof.com/appgini/help/advance ... ific-hooks

Code: Select all

function tablename_after_delete($selectedID, $memberInfo, &$args){
	// log file
	$logFile='deletes.log';
	
	// attempt to open the log file for appending
	if(!$fp = @fopen($logFile, 'a')) return;
	
	// write log data: date/time, username, IP, record ID
	$datetime=date('r');
	fwrite($fp, "$datetime,{$memberInfo['username']},{$memberInfo['IP']},$selectedID\n");
	fclose($fp);
}
Just edit the hook accordingly to suit your purpose and add it to your tablename.php file in the hooks folder under the tablename_after_update() function.

Hope that helps.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2013-04-25 17:13

Thank you sooooooo much!!!
Tina O'Connell
Web Dev & Appgini FAN

User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Re: History of Field changes - Logging how??

Post by shasta59 » 2013-06-23 01:47

I do this but I write the changes to another table in my database. This enables me to search and use the data rather than having to search a text file. It records the table name, date, time, user name, field(s) changed and more. I also have it record the previous data in the field and the new data so you can see what has changed. Some overhead involved in this but it give a running record of what was changed over time.

This insures if a serial #, for example, was changed accidentally, the original entry is still available by looking in this other table. It makes one new entry for each field changed. It does record a lot of data but is not really a problem and performance has not been affected. It is kinda like backing up the data only this records it in a separate table and is easier to access.

In one instance it caught some changes being made by someone with less than honest intentions regarding the filing of reports. The hooks files are used to record this info as it gets the existing data before updating and then gets the new data after updating. The tough part was making the comparison coding work fast enough as it had to check 50 fields, for example, to see which one(s) changed. (We are talking milliseconds of time so not a big deal really).

Again, this code is not really ready for sharing but does work like a charm. It is another one of those things that if I get time I will share it here and make it generic.

Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2013-10-15 19:15

Alan,

I do need that to write to a separate table.. can you please send me that to email.. [email protected]

Thanks,
Tina
Tina O'Connell
Web Dev & Appgini FAN

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2014-03-04 13:20

Alan,

Any luck?? I really need this solution asap. Would be most grateful. Thanks, Tina
Tina O'Connell
Web Dev & Appgini FAN

stevelizardi
Posts: 5
Joined: 2014-02-27 20:47

Re: History of Field changes - Logging how??

Post by stevelizardi » 2014-04-24 17:49

When a note is updated, is there a way to append it to another table? So If we are tracking a customer, it would update and when you see the customer you would see the notes associated with that?

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2014-04-28 14:06

Actually I got this to work and it saves EVERYTHING to a new table in the same database.. here is my code. I put it in the hook tablename_after_update and tablename_after_insert

insert works when new record is added and update works when any record in that table is modified.

Code: Select all

function routes_after_update($data, $memberInfo, &$args){

// we need to iterate through the $data array

$RouteNo=$data['RouteNumber'];
foreach($data as $field => $value){
$messageData .= "$field: $value \n";
}

$recID=mysql_insert_id();
sql("insert into membership_userrecords set tableName='routes' , pkValue='#recID', 
memberID=' ".getLoggedMemberID()."', dateAdded=' ".time()."', dateUpdated='".time()."',
groupID= ".getLoggedGroupID().".", $eo);
 

//insert into historical log data from before update and after update

sql("INSERT INTO `Historical_Log` (`RouteNumber`, `old_data`, `new_data`, `ChangedDate`, `ChangedBy`) VALUES('{$data['RouteNumber']}', '{$CapoldData}', '{$messageData}', '{$data['LastUpdated']}', '{$memberInfo['username']}')", $eo);
return TRUE;

    		return TRUE;

	}
old data part does not work.. but the new data works perfectly.

Good luck.
Tina O'Connell
Web Dev & Appgini FAN

stevelizardi
Posts: 5
Joined: 2014-02-27 20:47

Re: History of Field changes - Logging how??

Post by stevelizardi » 2014-04-28 18:00

Will this also show the data when you review the record? Thanks for your help.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2014-04-29 13:33

No only when it is updated if you put it in tablename_after_update
and only when a new record is inserted when you put the code in tablename_after_insert

I don't know how to log reviewers yet.. interesting.. I know the membership user records logs what records users update and when they log in and out but not sure what they are just looking at.

Why do you need that?
Tina O'Connell
Web Dev & Appgini FAN

stevelizardi
Posts: 5
Joined: 2014-02-27 20:47

Re: History of Field changes - Logging how??

Post by stevelizardi » 2014-04-30 13:10

We need it to keep track of Patient information for referrals. it seems like the log that Appgini provides will work as well.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: History of Field changes - Logging how??

Post by toconnell » 2014-05-08 21:34

I did get this to work..

Just used simple INSERT INTO sql query..

Code: Select all

	
$LogChanges=mysql_insert_chng();

sql("INSERT INTO 'Historical_Log' SET RouteNumber='{$data['#recID']}', ChangedBy='".getLoggedMemberID()."', ChangedDate='".time()."', old_data='{$messageData2}', new_data='{$messageData}', $eo");
Now where I have RouteNumber that is one of my index fields.., the ChangedBy is the user who updated the record.. thats exactly how yours should appear if you want to capture that. Changed date is when it was updated, the old data does not work yet but the new data, I just used the message data.. from this code:

Code: Select all

foreach($data as $field => $value){
$messageData .= "$field: $value \n";
}
but instead you could put insert {$data['notes']} into your log table field name notes.. and it would be like this...

Code: Select all

	
$LogChanges=mysql_insert_chng();

sql("INSERT INTO 'Historical_Log' SET RouteNumber='{$data['#recID']}', ChangedBy='".getLoggedMemberID()."', ChangedDate='".time()."', notes='{$data['notes']}', otherfieldname you create in log table='{$data['otherfieldnameyou want to capture']}', $eo");
I hope that helps..

Thanks, Tina
Tina O'Connell
Web Dev & Appgini FAN

Post Reply