[DjDark1]

Ok, this may be a bit hard to explain but bare with me. I'm creating a website, currently when you login you are added to the active user list and when you log out you are removed. Also if you aren't active within 15 minutes (time editable) you will be removed from the active users list until you make an action on the website (For that to work, another user has to be active on the website - will set up cron jobs later).

The thing I'm trying to do the now is check how long you are signed in for and how long you signed in for based on the above system. When you login, you are given a login key. What I tried to do was when you were added to the active user system you created a row in my usermap table with when you logged in and this login key then when you logged out, this row would update with when you logged out and how long you were logged in for e.g. 20 minutes. Problems occur when you stay logged in for example overnight and you're not active so when you sign out the next day, my row says you have been logged in for 24 hours.

Now, what I'm trying to do is when you are removed from the active users table for being inactive, update my usermap row and then when you were active again, add a new row until your either inactive or log out. Now the problem arises as these rows use your login id to distinguish you from previous logins and other users but when you are inactive you still have the same login id unless you log out so I can't update my rows. Here are the following pieces of code:

 	 function addActiveUser($username, $time){
         global $session;
         $zone = 'GMT';
         $currentdate = $today = date("l, F jS Y, H:i:s ") . $zone; //Today
         $userip = $_SERVER['REMOTE_ADDR'];
         $r = "SELECT * from ".TB_USERMAP." where login_id = '$session->userid' and username = '$username'";
         $result = mysql_query($r, $this->connection);
         if(mysql_num_rows($result) == 0){
            //Only insert this once per login
            $t = "INSERT INTO ".TB_USERMAP." (login_id, username, date_in, timestamp_in, ip) VALUES('$session->userid', '$username', '$currentdate', '$time', '$userip')";
            mysql_query($t, $this->connection);
         }
}

Also another problem arises here, if it's the first time you ever login, two rows are added. Any other login, only one row is added. What's wrong there?

	  function removeActiveUser($username){
	     global $session;
         $this->removeUsermap($username);
	  }

    function removeUsermap($username){
        global $session;
        $time_logged_out = time();
        $zone = 'GMT';
        $currentdate = $today = date("l, F jS Y, H:i:s ") . $zone; //Today
        $p = "SELECT * from ".TB_USERMAP." where login_id = '$session->userid'";
        $result = mysql_query($p, $this->connection);
        $result2 = mysql_fetch_array($result);
        $time_logged_in = $result2['timestamp_in'];
        $elapsedTime = ($time_logged_out - $time_logged_in);
        $totaltime = date('H \h\o\u\r\s, i \m\i\n\s \a\n\d s \s\e\c\o\n\d\s', $elapsedTime);
        $t = "UPDATE ".TB_USERMAP." SET date_out = '$currentdate' WHERE login_id = '$session->userid'";
      	 mysql_query($t, $this->connection);
        $u = "UPDATE ".TB_USERMAP." SET timestamp_out = '$time_logged_out' WHERE login_id = '$session->userid'";
        mysql_query($u, $this->connection);
        $v = "UPDATE ".TB_USERMAP." SET time_logged_in = '$totaltime' WHERE login_id = '$session->userid'";
        mysql_query($v, $this->connection);
    }

Now this all works fine. Here is my removeInactive users code.

	  function removeInactiveUsers(){
	     global $session;
	     $timeout = time()-USER_TIMEOUT;
         $r = "SELECT * FROM ".TB_ACTIVEUSERS." WHERE timestamp < $timeout";
         $result = mysql_query($r, $this->connection);
         $results = mysql_fetch_array($result);
         if($results['username'] = $session->username){
            $this->removeUsermap($session->username);
         }
	  }

That doesn't work but even if it did, how would a new row become added when the user becomes active again since $session->userid hasn't changed. What I want is a personal id which changes each time you become active and is unique to each person and each activity.

Feel free to ask any questions if you don't understand.
Sorry for the long winded question but this has been bugging me for a while and thanks for your time.

All help will be appreciated.

[ghqwerty]

are you simply trying to have a little comment when they log out saying you were 'ACTIVE' for x mins ? if so you are majorly over complicating things.

if not please explain what you want to have done, not how you are currently doing it. then i might be able to suggest an easier work around.

regards

[DjDark1]

Ok, I'm trying to create a usermap table for admins to see. Maybe set it up for users later on. What I want to do is record each time you become active and inactive and how long you were active for such as:

DjDark1
Active on: 23/04/2010 20:49
Active off: 23/04/2010 21:18
Active for: 29 minutes 12 seconds
IP: xxx.xxx.xxx.xxx

etc.. for each time your active and for each member. Why IP is added is do we can keep a record of how many times an IP has logged in, which users used it and if an odd case pops up (such as different user logs on an IP another user commonly uses). I also plan to use this table to create a user level system later on where the points they gain for levelling is partially based on how long they were active.

Do you understand better now?

[ghqwerty]

yes that makes it much clearer however, when they log on say twice, the last log on time will overwrite the current log on.

would it not be more advisable to have
user :
last log on :
last log off :
last online duration :
total online duration :
ip :

i take it that your problem is that if a user leaves their account on over night then there active time will be 12 hours + when in reality it may be more like 1 hour ?

have i understood you correctly ?

[DjDark1]

Well I would prefer it set so I can see each and every time they log on not just the most recent.

Yes that's exactly my problem, I have some pieces of code which I have missed out in the above snippets that when a user becomes inactive, they are removed from the active users table. I would like this same logic to apply to the usermap table but the trouble is creating a new row because to differentiate between other users and other logins, I use their login id (and as long as they are logged in - they still have the same login id).

Also as I said earlier for the first time a user ever logs in, two rows are added: One with the login id (the one that updates when you log out) and one without the login id which doesn't update. After this though, each time you login only one row is added. I can't seem to figure what in my code sets that off.

Sorry for being so confusing but I hope you understand better now

[ghqwerty]

haha, well youve confused me more now.

ive written a lot here however, i think youll find it useful. ask me to explain any part of it.

what i would do is this. scrap most of your current code. it is very confusing, i sugeest indenting better. im guessing your relatively new to OOP as well as your code could be far more efficient. any this is what i would do.

have you usermap table with the following columns

loginId (this is the primary key and is auto incremented)
loginUser (this is the user's id number)
loginTime
lastAction
logOffTime
ip

when a user logs in add the relevent data. this is the easy part. also for last action make it equal to the current time, date('Y-m-d H:i:s'). add the loginId into a gloabl variable
$this->loginId;

now also check this timestamp BEFORE YOU UPDATE IT. so query the database and then if the last action was more than say 10 mins ago destroy the session and log them off. you can use this time to then work out approximately when the became inactive.

$setBack = strtotime($this->usermap['lastAction']) - mktime();//negative number 
$lastActive = mktime() + $setBack;//timestamp of last active time 
 

this can then be used to make your logOff times more accurate as you can then take the last active timestamp and then make this the logOff time instead of the current time.
you can then use this time and the log on time to calculate a more accurate last active time.

at the top of every page call this function, i see you using OOP so this shouldnt be to hard just add this function to your base class.

function updateOnline(){		
		$query = "update usermap set lastAction = '". date("Y-m-d H:i:s")."' where loginId = '".$this->loginId."'";
		$dbdata = mysql_query($query, $this->dblink) or die($this->mysqlErrorCode('updateOnline', 1));	//take this bit out if you need, its my personal mysql error system. helps debugging				
	}
	

theres a lot here, but you will still need to do some more coding yourself to make it work properly however this is the basis of what you need and the rest should be easy stuff.

ive wrote a lot here but if you need any help, just ask.

[DjDark1]

Ok, I think you're understanding better but you're still missing the key parts. Here is how the usermap table is set up:

id (Auto increments)
login_id (The login id you are given when you login)
username
date_in (Date and time you become active)
date_out (Date and time you become inactive)
timestamp_in (Timestamp when you become active)
timestamp_out (Timestamp when you become inactive)
time_logged_in (How long you were active for)
ip

As I have said above this works for logging in and out but the problem arises as you said when you leave it overnight.
What I am trying to do is to replace login_id with a unique id to each active time for each user so when they become inactive, this key is destroyed and the field updates then when they become active again a new key is created.

I can work out if a user hasn't been active in the last 15 minutes but I can't destroy the login id without logging them out.

Hopefully that should clear things up a bit

[ghqwerty]

date in and date out can be got from the timestamp, thats just useless data.

best way to make it unique is to make it the primary key. then they cant be the same.

you dont understand what i just posted then, that will do exactly what you wanted in a more efficient way.

if they have been inactive for more than 15 minutes make them re-login in. its there fault.



so when they re-login in a new row is added. and the old row updated to be accurate to when they last were active.

im off to sleep now. keep working at it and it will work eventually. if not ill help you again in the morning.

im fairly confident that my post is infact what you wanted you just need to add a few bits to it to get it exactly how you want it.

This post has been edited by ghqwerty: 23 April 2010 - 02:15 PM

[DjDark1]

I do understand what you said but I don't want to log them out, as for id that's the primary key. I'm not quite sure what's it's purpose is here but I use it for everything so I just see how many records there is.
Date out and in are pointless but I just use them to save time later. Also for last active, that updates in the users table when they become active. It's a missing piece of code under addActiveUser where when they become active, it updates.

What I'm looking for is an alternative to $this->userid which can be destroyed and re-created everytime someone becomes inactive then active again.

Sorry for being confusing again

[ghqwerty]

without someone refreshing the page that is impossible, as $this->userid is a variable held in the script.

you can make it so you dont log them out, just dont destroy the session.


so you want to be able to work out a users active time (not just online time) without logging them off.
add an extra field 'lastUpdate'

user logs in -> log in time is recorded -> user requests a page -> time since last active is calculated -> this time is added to active time count, last update reset to current time -> if user is inactive for more than 10 minutes when they refresh the page work out last active time using the code above, take the last update away from this, add the time to active online time.

that something like what you want ?

[baavgai]

I'm confused by a few things here. First, you start with $session->userid. This is the currently authenticated user? Then why do you need the user name, too? If you're actually using sessions, why not just keep the key to the current login info and go from there?

Your usermap table is also throwing me:

id (Auto increments) : good
login_id : how is this any different from id?
username : you don't have a user table with a user_id?
date_in : fine
timestamp_in : this is different from date_in how?
-- date_out : you can't know this
-- timestamp_out : ignored
date_last: this you can know
time_logged_in : you understand you'll have to figure this out later?
ip : fine

Let's take that and make a new table, login_audit

login_audit_id (Auto increments)
user_id
ip
date_login
date_last

Now for some code.

function updateActiveUser() {
	global $session;
	
	// the session should have the audit_id, if not we fix it.
	// No need for a select
	$auditIdSessionKey = 'audit_id';
	if (!isset($_SESSION[$auditIdSessionKey])) {
		$sql = 'INSERT INTO login_audit(user_id, ip, date_login, date_last)'
			. " VALUES ($session->userid, '" . $_SERVER['REMOTE_ADDR'] . "', NOW(), NOW() )";
		mysql_query($sql, $this->connection);
		$_SESSION[$auditIdSessionKey] = mysql_insert_id($this->connection);
	} else {
		$sql = 'UPDATE login_audit'
			. ' SET date_last = NOW()'
			. " WHERE login_audit_id=" . $_SESSION[$auditIdSessionKey];
		mysql_query($sql, $this->connection);
	}
}

That's it. That's really all you need to do. Call updateActiveUser() on every page. When you log out, make sure you clear the session.

I feel part of the problem here is a lack of SQL understanding. e.g.

$t = "UPDATE ".TB_USERMAP." SET date_out = '$currentdate' WHERE login_id = '$session->userid'";
 mysql_query($t, $this->connection);
$u = "UPDATE ".TB_USERMAP." SET timestamp_out = '$time_logged_out' WHERE login_id = '$session->userid'";
mysql_query($u, $this->connection);
$v = "UPDATE ".TB_USERMAP." SET time_logged_in = '$totaltime' WHERE login_id = '$session->userid'";
mysql_query($v, $this->connection);

// Though I question the logic of the process in general.
// This can, and should, be:
$sql = "UPDATE " .TB_USERMAP 
	. " SET date_out = '$currentdate'"
	. ", timestamp_out = '$time_logged_out'"
	. ", time_logged_in = '$totaltime'"
	. " WHERE login_id = '$session->userid'";
mysql_query($sql, $this->connection);

Now, I hear you asking, "how do I find the active users?"

First, you have to define how long an active session is. Let's say five minutes. Then this is a list of all the active sessions:

select * 
	from login_audit
	where date_last > ( NOW() - INTERVAL 5 MINUTE ) -- don't blame me for that, mysql syntax

You should have a users table with things like user_id, username, passhash, etc? You want to see the currently logged in users by username?

select b.username, a.ip, a.date_login, a.date_last, timediff(a.date_last, a.date_login) as ElapsedTime
	from login_audit a
		inner join users b
			on a.user_id=b.user_id
	where a.date_last > ( NOW() - INTERVAL 5 MINUTE ) -- don't blame me for that, mysql syntax

You can play around with CASE and NOW() to get the above a little prettier, but I'll leave that to you.

Hope that helps.

[DjDark1]

Right ok, I know I have been a bit confusing but hopefully this should clear things up.

$session->user_id is different for every user and every login, it's and id I use to help create cookies and does not contain any data about the user. I only used in my script here so I could create a unique row in the database.

Here is how the script works right now. Every page on the website accessed session.php and from the start certain functions are ran every time any page is clicked on. (I'm making an assumption that I will have an active user at every point in the day and if not I can make cron jobs do this but that's not the point right now). So what happens is you log on and you're added to the activeuser table and added to the usermap table. If you're unactive within 15 minutes, you are removed from the activeuser table until you do something again but the problem arises in stopping the usermap row here and creating a new usermap row for when you are next active.

ghqwerty is on the right idea but a problem with his idea is that say you are active at 4pm and stay active till 4.30pm and then become inactive but stay logged in and then without logging out/back in later, your last active row becomes 9m for example saying you have been active for 5 hours and it's really 30 mins.

A quick solution I was looking for was just a variable I could set up that would be unique for each user and would stay with them until they become inactive (variable destroyed).

@baavgai: Regarding the usermap table, yes timestamp and date are basically the same but that's just the way I work (Yes, I like to avoid problems later). As for date_out, say you signed in (became active at 11.50pm) and stayed active 12.20am, your date has changed (I'm just covering all actions).

Can you see what I am trying to do now, I'm not looking for anything complicated just a row for each activity each user has in my database.

Any more questions feel free to ask

[ghqwerty]

my last post was the solution to that.

12->action
12.03->action
last action 12.03
last update 12.00
gap less than 10 minutes add 3 mins to active time (180 seconds)
update last update to now
12.05->action
last update 12.03
last action 12.05
gap less than 10 mns add 2 minutes to the active time (120 seconds)
update the last update to now
12.25->action
last action 12.25
last update 12.05
gap is more than 10 minutes add 3 minutes to active time to compensate (they may have been on for a few mins then gone)
reset last update to now


does that help explain ?
when a page gets called update last action, then use this against last update to work out the gap in between and then use that to evaluate the active time.

[DjDark1]

That is one solution yes, it's probably the solution I will end up going for. Although at the start I wanted to know the exact times user's were active between such as multiple rows in the database. Is there a way still to do that?

Also, I mentioned earlier in my addActiveUser, why does it add two rows?

Thanks for all your help

EDIT: Also reading through your code, could you elaborate on this part: "gap is more than 10 minutes add 3 minutes to active time to compensate (they may have been on for a few mins then gone)".

This post has been edited by DjDark1: 25 April 2010 - 03:39 AM

[ghqwerty]

you can do that, the same way i was doing it but just with a change. when they have been inactive for more than ten minutes just end that row and then make a new row for the current time.

what i mean is if even if the last active time is 10+ mins before they may have been on the same page for a few mins and then logged off. just add a couple of mins to compensate for it. but its up to you