tricky users online script

[Aesop]

Hello everybody,

Recently I have tried to implement a feature that shows whos currently online based off of info passed to my mysql database. In the login validation script it updates my users table with the time they logged in via timestamp. Then on the admin page I can see who's logged in. The problem is that it shows me all the users instead of reading the column from the table correctly. I also have a spot on the page that sends out a welcome message to the currently logged in user (which would be me) but instead of saying "hello aesop" it shows the name of the newest member record. I suspect my session variables are to blame...

Heres where the begins begins on the login validation script

require 'globals/dbconnect.php';

session_start();
$process_login = "SELECT * FROM users WHERE user_name='$user_name' AND password='$password'";

//BEGIN TIMESTAMP UPDATE
$time = date("YmdHis");
$query = "UPDATE users SET lastaccess = '$time' WHERE user_name = '$user_name' AND password='$password'";
//END TIMESTAMP UPDATE

$result = mysql_query($query) or die("Query failed");
$login_result = mysql_query($process_login) or die (mysql_error());  

if(mysql_num_rows($login_result) > 0){
$result = mysql_fetch_array($login_result);
$groups = $result['groups'];
switch($groups) {
case "cbragent":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: cbr/cbragent.php");
break;
case "cbrclient":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: cbr/cbrclient.php");
break;
case "cpmagent":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: cpm/cpmagent.php");
break;
case "cbguest":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: cbguestacct/client.php");
break;
case "cbadmin":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: admin/index.php");
break;
case "cpmclient":
$_SESSION['groups'] = $groups;
$_SESSION['logged_in'] = TRUE;
$_SESSION['user_name'] = $user_name;
header("location: cpm/cpmclient.php");
default:  header("location: login.php");
break;}
};
?>

It matches the timestamp to the user just fine. Now onto the snippet that calls for currently logged in users

$time = date("YmdHis");
$time2 = $time - 200;
/* Performing SQL query */
$query = "SELECT DISTINCT * FROM users WHERE lastaccess > '$time2'";
$result = mysql_query($query) or die("Query failed");

while($line = mysql_fetch_array($result))
{
extract($line);
print("<li>$real_name</li>");
}

Would the existing session variable user_name be screwing this up somehow or should I be taking a different approach entirely??

[Aesop]

does this make sense? Is there any other info I can add that I missed??

[wilku]

If you use timestamp, use time() instead of date to create your $time and then $time2. The timestamp for 2005-06-02 03:04:15 isn't 20050602030415. It's the number of seconds from some date in early seventies (Unix epoch).

[Aesop]

ok, so I changed the database column "lastaccess" to TIME. I have been playing with the actual php to try and figure out what should be done to fix this....

Heres what I have got with the results

login validation script ~action:

$process_login = "SELECT * FROM users WHERE user_name='$user_name' AND password='$password'";
$time = time();

page to display users ~ action

$time = time();
$time2 = $time - 200;

/* Performing SQL query */
$query = "SELECT DISTINCT * FROM users WHERE lastaccess > '$time2'";
$result = mysql_query($query) or die("Query failed");

while($line = mysql_fetch_array($result))
{
extract($line);

print("<li>$real_name</li>");
}

reasult~ nothing gets displayed and the database shows as 838:59:59

same thing happens when I change the time to ("H:i:s"). If I try to format it as ("HⓂs") and login as myself, the session variable user_name thinks that I am the next user record and displays me as that person and the database shows as 838:59:59. I am so damn confused over this whole thing.... :glare:

How should I be formatting the TIME()?

[Aesop]

LOL!!!

Ok, so I figured this out. To fix this I changed the column in the db from timestamp (and I tested time as well) to an INT format allowing for 10 characters. time() only gives 10 characters!

However, a new problem came up. After a certain amount of time it shows that I am no longer online due to the part of the code that says

$time2 = $time - 200;

How do I update the time whenever a user goes to a different page?

[wilku]

if you have now your last acces as int now, put the following on every page, where you want time to update

//BEGIN TIMESTAMP UPDATE
$time = time();
$query = "UPDATE users SET lastaccess = '$time' WHERE user_name = '$user_name' AND password='$password'";
//run the query now
//END TIMESTAMP UPDATE 

Change the where clause to however you identify logged in user.

[Aesop]

right on that works! Funny thing is I figured it out about 5 minutes later but just wanted to get out of here and get home from work :rolleyes:

Now the final piece I need to add is a timeout feature after 5 minutes. Heres what I got but it aint workin. The database doesnt update so I dont get redirected after 5 minutes....

I put this new session variable in my login validation script

$_SESSION['lastaccess']= mktime(); 

Then on the top of the pages I put

session_start();
$user_name = $_SESSION['user_name'];
require '../globals/dbconnect.php';
$time = time();
$query = "UPDATE users SET lastaccess = '$time' WHERE user_name = '$user_name' AND password='$password'"; 
$newtime=mktime();
$tdiff=$newtime - $_SESSION['lastaccess'];
if ($tdiff > 300)
{
header("Location:../../login.php");
exit;
}

to save myself some trouble I plan on putting all of this including the database connection settings into 1 file and doing an include on each page. But first I have to get this working.... :queasy: