0

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.

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

3
Contributors
7
Replies
8
Views
6 Years
Discussion Span
Last Post by styleest
0

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.

0

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

0

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).

0

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

0

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.

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.