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]
History of Field changes - Logging how??
History of Field changes - Logging how??
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: History of Field changes - Logging how??
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
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.
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);
}
Hope that helps.
Re: History of Field changes - Logging how??
Thank you sooooooo much!!!
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
Re: History of Field changes - Logging how??
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
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 -
Re: History of Field changes - Logging how??
Alan,
I do need that to write to a separate table.. can you please send me that to email.. [email protected]
Thanks,
Tina
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
Web Dev & Appgini FAN
Re: History of Field changes - Logging how??
Alan,
Any luck?? I really need this solution asap. Would be most grateful. Thanks, Tina
Any luck?? I really need this solution asap. Would be most grateful. Thanks, Tina
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
-
- Posts: 5
- Joined: 2014-02-27 20:47
Re: History of Field changes - Logging how??
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?
Re: History of Field changes - Logging how??
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.
old data part does not work.. but the new data works perfectly.
Good luck.
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;
}
Good luck.
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN
-
- Posts: 5
- Joined: 2014-02-27 20:47
Re: History of Field changes - Logging how??
Will this also show the data when you review the record? Thanks for your help.
Re: History of Field changes - Logging how??
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?
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
Web Dev & Appgini FAN
-
- Posts: 5
- Joined: 2014-02-27 20:47
Re: History of Field changes - Logging how??
We need it to keep track of Patient information for referrals. it seems like the log that Appgini provides will work as well.
Re: History of Field changes - Logging how??
I did get this to work..
Just used simple INSERT INTO sql query..
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:
but instead you could put insert {$data['notes']} into your log table field name notes.. and it would be like this...
I hope that helps..
Thanks, Tina
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");
Code: Select all
foreach($data as $field => $value){
$messageData .= "$field: $value \n";
}
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");
Thanks, Tina
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN