Hi fellow programmers, i am designing a Student management system with PHP MySQL and using dreamweaver for editing. I need to do audit trails; that is to capture every operation any users does on my website.

I was able to capture the logging in using the codes below but i couldnt capture log out, i need help please

Below is the code:

*****************************************************************************************

<?php
// assume that administrator has logged in to system to perform
user-administration tasks // admin username is stored in a session
variable by default // this is useful for audit purposes
session_start(); $_SESSION['LOGGED_IN_USER'] = "john";
// add a new user
function addUser($user, $pass, $perms)
{
// open connection to database
$connection = mysql_connect("localhost", "joe", "pass") or die
("Unable to connect!");
mysql_select_db("myapp") or die ("Unable to select database!");
// formulate and execute query
$query = "INSERT INTO users (user, pass, perms) VALUES('$user',
'$pass', '$perms')";
mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
// log activity to audit database
audit("ADD_USER", $_SESSION['LOGGED_IN_USER'],
"$user:$pass:$perms", addslashes($query));
// close connection
mysql_close($connection);
}
// edit an existing user
function updateUser($user, $pass, $perms)
{
$connection = mysql_connect("localhost", "joe", "pass") or die
("Unable to connect!");
mysql_select_db("myapp") or die ("Unable to select database!");
// formulate and execute query
$query = "UPDATE users SET pass = '$pass', perms = '$perms'
WHERE user = '$user'";
mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
// log activity to audit database
audit("UPDATE_USER", $_SESSION['LOGGED_IN_USER'],
"$user:$pass:$perms", addslashes($query));
// close connection
mysql_close($connection);
}
// delete an existing user
function deleteUser($user)
{
$connection = mysql_connect("localhost", "joe", "pass") or die
("Unable to connect!");
mysql_select_db("myapp") or die ("Unable to select database!");
// formulate and execute query
$query = "DELETE FROM users WHERE user = '$user'";
mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
// log activity to audit database
audit("DELETE_USER", $_SESSION['LOGGED_IN_USER'], "$user",
addslashes($query));
// close connection
mysql_close($connection);
}
// generic audit function
// logs all activity to a database
function audit($op, $owner, $args, $msg)
{
$connection = mysql_connect("localhost", "root", "pass") or die
("Unable to connect!");
mysql_select_db("trails") or die ("Unable to select database!");
// formulate and execute query
$query = "INSERT INTO audit (timestamp, op, owner, args, msg)
VALUES (NOW(), '$op', '$owner', '$args', '$msg')";
mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
}
addUser("joe", "joe", 3);
addUser("sarahh", "bsdfg49", 1);
updateUser("joe", "joe", 4);
deleteUser("sarahh");
addUser("sarah", "bsdfg49", 1);
?>

Here's a snippet from the audit table:
+---------------------+-------------+-------+
| timestamp | op | owner |
+---------------------+-------------+-------+
| 2002-11-26 08:28:05 | UPDATE_USER | john |
| 2002-11-26 08:28:05 | DELETE_USER | john |
| 2002-11-26 08:28:05 | ADD_USER | john |
| 2002-11-26 08:33:14 | ADD_USER | joe |
+---------------------+-------------+-------+
This audit table can then be queried to obtain detailed information on the activities
For example,
mysql> SELECT timestamp, op, args FROM trails WHERE timestamp >=
mysql> 2002-11-26
AND owner = 'joe';
+---------------------+-------------+------------------+
| timestamp | op | args |
+---------------------+-------------+------------------+
| 2002-11-26 08:33:29 | ADD_USER | joe:joe:3 |
| 2002-11-26 08:33:29 | ADD_USER | sarahh:bsdfg49:1 |
| 2002-11-26 08:33:29 | UPDATE_USER | joe:joe:4 |
| 2002-11-26 08:33:29 | DELETE_USER | sarahh |
| 2002-11-26 08:33:29 | ADD_USER | sarah:bsdfg49:1 |
performed by the various users, sorted by time or type of activity.

For future reference please use the code tags.

As for your question, following your programming scheme you can create a logoff function which will insert the log off information in the database before any code that will remove the session or whatever you are doing to log the user off.

Thanks so much, infact that is exactly what i am trying to achieve but i need the code/function that wil insert it

Please, i still need your help on the function that wil do it

Just copy/rename one of the functions you already have. Then make sure it gets called when your user hits the logout link (or whatever you have).

Yeah, i've already done that and it works out fine, my problem is that when i'm trying to generate a report... i need to reference both id's from the log in and log out table, what i need is an SQL statement thet will link both

Login and logout are not logged in the audit table ?

You need to specify your tables, and what you want as output before we can start guessing. An example would be nice and helpful.

I was able to capture the login and the logout time using the functions i told u above, but i dont know how to link both information to create a report cos they are from different tables

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.