MySQL Active User

[fnbcprog]

Thanks for checking this out. What I am trying to do is build a system of emplyee logins where when an employees logs in it sets the status equal to 'Y' and when they logout it sets the status equal to 'N'.

Well what happens when they forget to logout and close the window? I need a function that on each login or logout check the database for users that have been idle for over an hour.

Can anyone help me with this...I know how to setup all the tables I just need the comparing of time function.

Thanks,

fnbcprog

[Jinks]

you could always just use a cron job ?? if your server supports it.. I am actually working on th same kind of thing myself..

What im doing is giving each user a timestamp of "last_online"

And it says this user is online for 10 minutes! Every page they view updates the "last_online" field. Then just go.... :

$online_timeout = 10

$year = date(Y);
$month = date(m);
$day = date(d);
$hour = date(H);
$minute = date(i) - $online_timeout;
$second = date(s);

$last_on = date ("YmdHis", mktime($hour, $minute, $second, $month, $day, $year));

$result = mysql_query("select * from user where last_online > $last_on order by last_online desc");

[EpicEro]

I forgot where I got the functions from, but I hope it can help you. You need to create a database to store your sessions as such:

CREATE TABLE session (
pk_session_iID int(11) NOT NULL auto_increment,
sUser text NOT NULL,
iTime int(11) NOT NULL default '0',
sIP text NOT NULL,
bGuest tinyint(4) NOT NULL default '0',
PRIMARY KEY (pk_session_iID)
) TYPE=MyISAM;

Next, you create a function that logs a user every time a page is loaded and deletes any records that have expired.

function setOnlineUsers(){
$sIP = $_SERVER["REMOTE_ADDR"];

if(session_register()){
    $sMemberName = $_SESSION["sMemberName"];
    $bGuest = 0;
}
else{
    $sMemberName = $sIP;
    $bGuest = 1;
}

//Past half hour;
$iTimePast = time()-1800;
$result = mysql_query("DELETE FROM session WHERE iTime < " . $iTimePast);

if(!$result){
    trigger_error("Problem removing old Sessions from database", E_USER_ERROR);
    return false;    
}

$result = mysql_query("SELECT iTime FROM session WHERE sUser ='" . $sMemberName . "'");

if(!$result){
    trigger_error("Problem selecting Sessions from database", E_USER_ERROR);
    return false;    
}

$iTime = time();

   if(mysql_numrows($result) == 0)
       $result = mysql_query("INSERT INTO session (sUser, iTime, sIP, bGuest) VALUES ('" . $sMemberName . "', '" . $iTime . "', '" . $sIP . "', '" . $bGuest . "')");
   else
    $result = mysql_query("UPDATE session SET sUser = '" . $sMemberName . "', iTime= '" . $iTime . "', sIP = '" . $sIP . "', bGuest = '" . $bGuest . "' WHERE sUser = '" . $sMemberName . "'");

if(!$result){
    trigger_error("Problem creating Session", E_USER_ERROR);
    return false;    
}
else
    return true;

}

Make sure that this function is in every page of the site.

And then you create a function to read the info of the database, include it on all the pages you want it displayed.

function whosOnLine(){
$l=connect();
$r=mysql_query("select * from session where bGuest = 0", $l);
$n=mysql_num_rows($r);

$r2=mysql_query("select * from session where bGuest = 1", $l);
$n2=mysql_num_rows($r2);

echo "<p>There are currently " . $n2 . " guest(s) and " . $n . " member(s) online</p>";

if(session_register())
    echo "<p>You are logged in as <br><b>" . $_SESSION["sUserName"] . "</b></p>";
else
    echo "<p>You are not logged on, Please register to use our services!<br><br>&nbsp;</p>";

}

Hope this helps!
Lee