2. PHPRunner General questions
  3. Audit table
This topic is locked

Audit table

 		6/25/2006 3:29:02 PM
PHPRunner General questions
O
 osluk author

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for audit_copy

-- ----------------------------

CREATE TABLE `audit_copy` (

`Ref` int(11) NOT NULL auto_increment,

`User` varchar(60) default NULL,

`IP` varchar(60) default NULL,

`DateTime` timestamp NULL default NULL on update CURRENT_TIMESTAMP,

`AccessType` varchar(60) default NULL,

`Details` varchar(255) default NULL,

`Flag01` int(10) default NULL,

`Notes` varchar(255) default NULL,

PRIMARY KEY (`Ref`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Generic audit log with admin flag checkbox and notes field';
-- ----------------------------

-- Records


Hi guys based on the posts about tracking changes I have got it working.

I want to capture some detail. Like the reord ref or name deleted and ideally the "data_list.php?a=search&value=1&SearchField=32&SearchOption=Equals&SearchFor=1&orderby=d47"
string so i can see what people are looking at most!
Any suggestions on improvements above gratefully received!
This is the log sort by desc date
View 43 gavin IP.IP.IP.IP 25/06/2006 14:13:39 Login OK

View 42 sysadmin IP.IP.IP.IP 25/06/2006 14:13:10 Record_Added

View 41 sysadmin IP.IP.IP.IP 25/06/2006 14:13:00 Record_Copied

View 39 sysadmin IP.IP.IP.IP 25/06/2006 13:51:12 Record_Deleted

View 40 sysadmin IP.IP.IP.IP 25/06/2006 13:51:12 Record_Deleted

View 38 sysadmin IP.IP.IP.IP 25/06/2006 13:50:52 Login OK

View 37 admin IP.IP.IP.IP 25/06/2006 13:50:37 Login OK

View 36 sysadmin IP.IP.IP.IP 25/06/2006 13:50:18 Login OK

View 35 admin IP.IP.IP.IP 25/06/2006 13:41:28 Login

View 34 gavin IP.IP.IP.IP 25/06/2006 13:39:44 Record_Modified

View 33 gavin IP.IP.IP.IP 25/06/2006 13:39:16 Login
I have the field "Details" ready to add the details of the transaction when I can figure out how to capture it!

I guess what I need to know is the syntax for capturing a field and if this is actually possible.
Cheers Chris
Alexey admin 6/26/2006

Chris,
you can capture the values entered by the user on Add and Edit pages by accessing $values array in Before record added and Before record updated events.
O
 		osluk author 6/26/2006

Chris,

you can capture the values entered by the user on Add and Edit pages by accessing $values array in Before record added and Before record updated events.


That is brilliant I would want the fields "Ref" "Chateau (Alpha format)" "Appellation" "Vintage"

How would I appended this dat into my "Details" field.
Cheers Chris
Alexey admin 6/27/2006

Chris,
add Save new data in another table action to your Before record added event and modify it to fit your needs.
O
 		osluk author 6/27/2006

I have this working (the details are captured into the audit table)
function AfterAdd()

{

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

global $conn;

$IP = $_SERVER["REMOTE_ADDR"];

$User = @$_SESSION["UserID"];

$AccessType = "Record Added";

$strSQLInsert = "insert into audit (IP,User,AccessType,DateTime) values ('$IP','$User','$AccessType',NOW())";

db_exec($strSQLInsert,$conn);
}
Thanks to everyone that helped with this.
==================
I dont fully understand the significance of the before add you mention above

is the syntax correct below to capture field Ref in data table into field details in Audit table.
Ideally I want a summary of several bits of data ref - chateau - appelation all concatenated into

the details field is this possible? if so what would the syntax be.
Thanks Chris
The intention is to capture the contents of the field "ref" so I can tell the record added or altered.
======================================
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strAudit;
$strSQLSave = "INSERT INTO AnotherTable (Details) values (";
$strSQLSave .= $values["Ref"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
O
 		osluk author 6/27/2006

function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$straudit;

$strSQLSave = "INSERT INTO audit ('Details','Detail_1') values (";

$strSQLSave .= $values['Ref','Appellation'];

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}


Is close but it does not like the line

Parse error: syntax error, unexpected ',', expecting ']' in /hsphere/local/home/bauduc-dev/bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php on line 34


Parse error: syntax error, unexpected ',', expecting ']' in /hsphere/local/home/bauduc-dev/bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php on line 34


This seems to be the offending line

$strSQLSave .= $values['Ref','Appellation'];


I'm sure I will spot inconsistencies one day but for now can someone see where this is going wrong!
Also : Is it OK to have the 2 different sections below - do they need to be combined in some way?

function BeforeAdd(&$values)

{

// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Insert a record into another table ****

global $conn;

$strSQLSave = "INSERT INTO data (`User created`,`Record added`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case

}
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn;

$strSQLSave = "INSERT INTO audit (Details,Detail_1) values (";

$strSQLSave .= $values['Ref','Appellation'];

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}


Thanks again for all the help
Cheers Chris

Final version removed to stop the error

Parse error: syntax error, unexpected ',', expecting ']' in /hsphere/local/home/bauduc-dev/bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php'>bordeauxreport.com/db-2005/AdminC/include/data_events.php on line 34
I can ftp straight into data_events.php and get it working just by removing this code - which must be getting closer - ??!!!
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn;

$strSQLSave = "INSERT INTO audit ('Details','Detail_1') values (";

$strSQLSave .= $values ['Ref','Appellation'];

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}