How to log an authenticated user (MySQL/ Apache)?

[Anon]

Hello,

Currently, I am using Apache, along with MySQL [using the auth_mysql_mod] to keep a list of users (almost 20,000)and passwords and authenicate them when they want access to the site.

My problem is, there are many users that have signed up that are taking up space in the database. I would like to remove those users, but have no way to track their login.

Is there a way to create another field in the users table that will record the date of a users last login? That way I may delete users that haven't logged in for a year or so. I'm not sure how I would go about doing that.

Any help you can offer will be appreciated!

~Justin Bright

[Anon]

You mean adding a column on the MySQL side? That'd be something like "ALTER TABLE user ADD COLUMN last_login date AFTER password". Or do you mean writing the code that will put a timestamp in there each time that record is accessed?

[Anon]

To insert the lastlogin.

After the user logs in, just before you redirect them to where ever ...

function loglastLogin($userid) {
global $dbname;
$SQLText = "UPDATE tablename set last_login = 'now()' where user = '$userid'";
if(!$res = mysql_db_query($dbname,$SQLText)) { print "ERROR: " . mysql_error();
} else {
// Do nothing ...
}
}
loglastLogin($userid);

This may help, I didn't test the code...
CT

[Anon]

I thought about doing it that way, but I would have to put that on each page wouldn't I?

I was wondering if there was a way to add the date when a user entered their username and password on the HEADER prompt only. That way, everytime they went to a new page, it wouldn't keep Updating the Database. I'm only interested in the date of their last login.

Thanks for your help.

~Justin

[Anon]

The actual code when the header authentication, which checks the mysql database, is requested. 🙂

Sorry for the confussion.

~Justin

[Anon]

Something similar to what Chauncey gave you for an example should work fine. You will not need to add that to all your pages. You will only need to include the example in the authentication part of your login script. In this way it will only log the login date when they enter their username and password.

Troy

[Anon]

Here is an example I've used ...
This may help...

<?
include('incs/db_connect.php');
$logintime = date("mdy HⓂs");
$query = "SELECT * from users where (name = '$name' and password = password('$mypassword'))";
$result = mysqlquery($db_name,$query) ;
if(!empty($result)) {
if (mysql_numrows($result) == 0)
{
print "Bad username password.";
} else {
$name = mysql_result($result,0,"name");
$password = mysql_result($result,0,"password");
$password = md5($password);

// Update the last time the user logged in...
//
$query = "UPDATE users set lastlogin = '$logintime' where name = '$name'";
$result = mysqlquery($db_name,$query) ;
if(!empty($result)) {
//
}else {
// echo mysql_error() . "<br>\n";
}
header("Location: ./phpscriptname.php");
}
//
}else {
// echo mysql_error() . "<br>\n";
}

?>

You may be able to insert this into your code...
You will need to modify it, since I just cut/pasted

Hope this helps
CT