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

Audit Log

 		1/9/2006 3:57:27 PM
PHPRunner General questions
prleo1 author

Admin,
I was wondering if I am able to track the changes made to the tables in my application by utilizing the new features in PHP Runner? For example, I have a table called audit, which I would like to populate with the username, IP Address, date-timestamp of the session that made the changes to my personnel table. Is this possible? If so, could you show an example?
Sergey Kornilov admin 1/12/2006

Hi,
you can do that using events fetaure.

At first please install the latest PHPRunner 3.0 version (build 80)
Here is how you can make Audit log.
Open Eventstab in PHPRunner. Select Table Events->Edit page->After record updated.

Then click Add actionand select Insert a record into another table.

Then modify the code to fit your needs.
Here is the sample code you can use:

function AfterEdit()

{

//********** Insert a record into another table ************
global $conn;

$ip = $_SERVER["REMOTE_ADDR"];

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

$strSQLInsert = "insert into AuditTable (ip, name,time) values ('$ip', '$name',NOW())";

db_exec($strSQLInsert,$conn);
}


Put the same code to Add page->After page is added event.
prleo1 author 1/13/2006

Thank you, Admin!

I setup the table, added the event and built a test project to test out the feature. I first tried the script with the login and it worked the first time(I queried my audit_table and the row was added). However, now that I have logged out, I am not able to login any longer. I keep getting the error
Fatal error: Duplicate entry '0' for key 1 in E:\app\web\wwwroot\staging\include\dbconnection.php on line 26
I, then cleaned out the sessions folder for php, then tried again. Same error.
Could you help me out?
I, also found this in my PHP log.
[13-Jan-2006 01:19:40] PHP Fatal error: Duplicate entry '0' for key 1 in E:\app\web\wwwroot\staging\include\dbconnection.php on line 26
..then, I truncated the audit_table and tried again. It worked. One row was added with the correct info and timestamp. It seems that once one row has been written by the event, no other row can be inserted in the table.
Sergey Kornilov admin 1/13/2006

Hi,
this error is related to unusual design of audit_table table.
You can post your audit_table creation script here and I'll help you to get rid of the error.
prleo1 author 1/13/2006

You are right. I left auto-increment unchecked. Thanks again, admin!
i appreciate your help.
PR
OCTheEagle 1/17/2006

Here is the sample code you can use:


function AfterEdit()

{

//********** Insert a record into another table ************
global $conn;

$ip = $_SERVER["REMOTE_ADDR"];

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

$strSQLInsert = "insert into AuditTable (ip, name,time) values ('$ip', '$name',NOW())";

db_exec($strSQLInsert,$conn);
}


Put the same code to Add page->After page is added event.


Hi,
I've got 2 questions about that script:

  • I guess global $conn contains the scripts to connect to the server and database?
  • Can you also use 'mysql_query' instead of db_exec?
    Reason I'm asking is because the following code gives me an error.
    Fouttype 2

    Fout beschrijving Missing argument 1 for listonload()

    URL localhost/proto2/personeelsvoorraad_list.php?

    Fout bestand C:\server\Apache2\htdocs\proto2\include\personeelsvoorraad_events.php

function ListOnLoad($strSQL)

{

//********** Custom code ************

// put your custom code here

global $conn;

$sql2 = "UPDATE personeelsvoorraad SET

Beschikbaar = contract+overwerk-nietbeschikbaar-ziek";
db_exec($sql2, $conn);

}
Sergey Kornilov admin 1/19/2006

Hi,
please see my answers:

I guess global $conn contains the scripts to connect to the server and database?



$conn is the result of mysql_connect function.

Can you also use 'mysql_query' instead of db_exec?



Yes, you can use mysql_... functions. When you work with MySQL of course.

Fout beschrijving Missing argument 1 for listonload()



Thank you for pointing me to this bug. We'll fix it in the next PHPRunner update.

To get your pages workinh now please modify C:\Program Files\PHPRunner\source\list.php file.

Replace ListOnLoad() with ListOnLoad($strSQL)
OCTheEagle 1/22/2006

Thanks, that did the trick <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=7639&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
prleo1 author 1/23/2006

Admin,
Thank you for the help with the Audit/Login Event. Is it possible to capture the logout information for my users. Userid from session, time of logout, IP address, etc.

I know that I will have to add an event to the Project, but do not know how I need to modify the Logout script on each page.

Thanks again,
PR
Sergey Kornilov admin 1/24/2006

Hi,
no, it's not possible.
The only way to track logouts is to use custom session handlers.

However it's quite complex task.
D
 		DouglasB 2/16/2006

great peice of info! I'll be using this for the CannaCare project!