2. PHPRunner General questions
  3. Logging record changes
This topic is locked

Logging record changes

 		11/21/2006 6:56:20 PM
PHPRunner General questions
A
 afdesigns author

I am creating a CMMS (Computerized Maintenance Management System) system using PHPRunner 3.1 build 162. The system is for a theatre department at a performing arts high school to keep track of technical equipment, its maintenance, and its hand out and return when the equipment is loaned out to groups to use. In the system, assigned students have logins that they do various transactions in within the system. The faculty wants to have a log of each login, record added, and destructive change made.
I have successfully used the events function of PHPrunner to log the successful and unsuccessful login attempts, using, $_Session['UserID'], and now(). However, I am having trouble figuring out how to log which record in particular has been edited, added, or deleted. I figure there is a variable of some sort that I can extract that of the primary key for the changed record. Any ideas? Thanks!
function AfterSuccessfulLogin()

{

//** Insert a record into another table ****

global $conn;

$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values (now(),'Successful Login', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);
}
J
 		Jane 11/22/2006

Hi,
you can use Before record added, Before record uodated and Before record deleted events for this purpose.

function BeforeAdd(&$values)

{

global $conn;

$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values(now(),'add record # ".$values["FieldName"]."', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);
return true;

}

function BeforeEdit(&$values, $where)

{

global $conn;

$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values(now(),'edit record # ".$values["FieldName"]."', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);
return true;

}

function BeforeDelete($where)

{

global $conn;

$str = "select * from TableName where ".$where;

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);
$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values(now(),'delete record # ".$data["FieldName"]."', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);
return true;

}



where FieldName is your actual field name where primary key is stored.

TableName is your actual table name.
A
 		afdesigns author 11/22/2006

Thanks! Little issue though. The primary key for the records that I want to track is an auto increment serial number that should not be edit'able. The only way I can get that number to show up on the log is to make it fully edit'able. Putting it on the edit page, but making it read only doesn't seem to do the trick either. Ideas?
J
 		Jane 11/22/2006

If you field is autoincrement use AfterAdd and BeforeEdit events.

Here is a sample code:

function AfterAdd()

{

global $conn;

$str = "select last_insert_id() from TableName";

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values(now(),'add record # ".$data[0]."', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);

}
function BeforeEdit(&$values, $where)

{

global $conn;

$str = "select * from TableName where ".$where;

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);
$strSQLInsert = "insert into _Log (Date_Time, Event, User_ID) values(now(),'edit record # ".$data["FieldName"]."', '".$_SESSION['UserID']."')";

db_exec($strSQLInsert,$conn);

return true;

}