nsukho
Posts: 10
|
| Posted: 09/12/2006, 5:43 AM |
|
I want to add an Audit Trail (Log) feature into my existing projects --- PHP/MySQL. Since CodeCharge Studio doesn't have this, I looked everywhere for it and I would like to implement this feature in my projects. Can you please help me to do? It won't just help me, but also the other developers using the CodeCharge Studio.
I will really appreciate your help on this important task.
Thanks,
Neil
|
 |
 |
datadoit.com
|
| Posted: 09/12/2006, 6:23 AM |
|
Neil, if you're on MySQL5, then look into using triggers. won't
tell you that using triggers keeps your database portable/transferable to
other database engines.
Another option would be to simply create a single audit log table that would
hold things like the table name, field name, user id, value before, value
after, and timestamp. Then, in CodeCharge's db_myql.php file make an update
that would write information to this table. Have not done this, but just a
concept to consider and research.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 09/12/2006, 6:44 AM |
|
Different applications may perform an audit trail differently. Some log the SQL statements, others log specific database transactions or field values, some log them to a text file, others log them to a database, some record additional user data (login, IP) and others don't. Thus you may need to decide on specific requirements for your needs.
One variant of an audit trail can be implemented by modifying the query($SQL) function in Common.php.
For example adding the echo statement below will display the output of every SQL statement that is executed. You can replace the echo with your own code or function that logs whatever you need into a file or database.
function query($SQL)
{
echo $SQL . "<br>";
$this->LastSQL = $SQL;
$this->NextRecord = array();
return parent::query($SQL);
}
(note: the echo may not work with INSERT/UPDATE statements because echo may be ignored when the page is redirected)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gaetgodi
Posts: 9
|
| Posted: 03/15/2007, 1:40 PM |
|
To create an audit tracking system for my tables I did the following:
I created a function "track" as follows and inserted it at the end of the common.php file
function track($trk,$Container)
{
$db = new clsDBFams5();
$qry = $Container->DataSource->SQL;
$dtechg = date("Y-m-d G:i:s");
// user_action_ID, set by $trk is 1 for inserts, 2 is for updates and 3 is for deletes
$action = $trk;
$SQL = 'INSERT INTO change_log(log_user_ID,date_changed,user_action_id,trk_after_change)'.'VALUES ('.CCGetSession('UserID').',"'.$dtechg.'",'.$action.',"'.$qry.'")';
$db->query($SQL);
// echo $SQL;
// echo '<br>qry is: '.$qry;
$db->close();
}
the clsDBFams5 is the class created by CCS for my connection Fams5
I inserted track(1, & $Container); as an event in each one of my record table events in the
After Insert, After Update and After Delete sections. The 1 changed to 2 and 3 for
update and delete respectively.
I created a MySql Table called change_log as follows:
changelog_ID int (primary key)
log_user_ID int
date_changed datetime
user_action_ID int
trk_after_change varchar
It works very well for me and thank you for the advice here, it helped steer me in the right direction.
|
|
|
|
|
