php, users online

dangerman

Hi,

i'm learning php + developing a site [just for fun, would never mess about like this on a proper site] with it at the same time, which is always a great laugh.

So I've got a login, when peeps login it sends their ip + their username + a timestamp to a table and then the page displays all the peeps logged in at that time.

My question is this: How do I kill that users entry in the db when they're gone. Granted if they press the logout button i can pass a DELETE * FROM table WHERE username='whatever' but if they just close off the window thats not gonna happen.

I'm thinking i need the session to expire, and when it expires, for that delete sql command to shoot off.

I know this is probably not the best way to do this, i've been hacking code together more than programming so far. - I just kind of 'arrived' at the above situation, rather than planning it. I used this tutorial:

http://www.oxyscripts.com/php/tutorials/18/0.php to get started, i've now hacked that to bits.

Ideas etc. muchos appreciated. If anyone has better alternative ways of doing this [what users are online right now...just like the vbulletin does] I'm very open to suggestions.

dahamsta

Do you log the last activity time in the users table? If not, you'll need set it every time the user loads a page, and then you can use that to expire a users session; via a cron job if the traffic to your site is intermittent, or by just building it into the script if it's regular. So it would be a case of running a regular job that checks for users that have a timestamp less than NOW minus (say) ten minutes.

adam

seamus

Just to expand (I think) on dahamsta's point a little.

If you have a table called "user" storing data for each user, such as password, email, etc. Have two more fields in there called "Last_known_ip" and "Last_logon_time".

When a user logs in, you store the ip and the current timestamp in those two fields.

I could be wrong - are you just allowing people to log in, i.e. no registering, just enter a username, like on IRC?

So to get a list of users who are logged on (and their IP) you only need to run
SELECT userid, username, last_known_ip, last_logon_time FROM user WHERE last_logon_time >= Now() - (10*60)

Change the Now() function for something equivalent that gets a timestamp for now.

dangerman

Hey lads, first up thanks for the help.

DELETE  FROM online WHERE hl >= Now() - (10*60)

'hl' is a mysql timestamp field.

It seems no matter what, the above line deletes all the entries from the table.

The table is literally: username, ip address + a timestamp of when they logged in.

Seamus why did u choose 10*60?
I see the logic behind the now() - whatever

...but it doesn't seem to work? At the moment whats happening is when one user logs in, it'll display him. But when the next one does, the Delete from online... line above deletes any other users, so basically at the mo it only displays 1 user online - the last guy to login/refresh the page, which executes that delete sql query. that delete line is deleting everything, not just older timestamps.

Change the Now() function for something equivalent that gets a timestamp for now.

Are you saying now() is incorrect? Because that was what was used in the original tutorial I followed. [link in my first post.]

Suggestions really appreciated. I haven't done any of this in so long, and that was with javabeans, hence my total crapness at this.

seamus

dangerman wrote:

DELETE  FROM online WHERE hl >= Now() - (10*60)

'hl' is a mysql timestamp field.

It seems no matter what, the above line deletes all the entries from the table.

Ok, the >= is the criteria I used for my select - that is, you want all users who have been here in the past 10 minutes, and you want to delete those who haven't. So....

The table is literally: username, ip address + a timestamp of when they logged in.

Seamus why did u choose 10*60?
I see the logic behind the now() - whatever

OK, a Data field of type TimeStamp is (I think, correct me here if I'm wrong), basically just a long integer field. A timestamp is usually just a sequence of numbers like 5632132184. It's the number of seconds from 12am on January 1st, 1970 to to a certain date. This means that more recent dates are larger numbers, and longer ago a timestamp was created, the smaller the number will be. The beauty I find of the timestamp is that it allows you manipulate and calculate dates with much more freedom than a field that just stores 12/12/2004 or something.

So what you're looking to do is delete all users who have a timestamp more than 10 minutes in the past. So you want to delete all users whose timestamp is less than Now() - ten minutes, or Now() - 600. Sorry the 10*60 is just habit, obviously it's 600 seconds.
So your delete should look like

DELETE FROM online WHERE hl < (Now() - 600)

Are you saying now() is incorrect? Because that was what was used in the original tutorial I followed. [link in my first post.]

I actually have no idea. I haven't tinkered about with MySQL in a few months (using Access for my web apps in work )

dangerman

Nice one lads, it's working a treat now.

A user logs in, stores all the info + a timestamp, if they refresh/go somewhere else the timestamp is updated. Then if the timestamp gets more than 6 minutes old, they're zapped outta the list. Poetry in motion.

Thanks for all the help. In conclusion, never underestimate my ability to stare at '<' and see '>' and vice versa for hours at a time.

Seth Brundle

is 10 minutes a bit restrictive?
I have found from the past that many users prefer timeouts of about 15 minutes

dangerman

Actually I was thinking of making it even shorter - any time the user clicks on any link/refreshes the timestamp updates, giving them another 10 minutes. I guess I'll just see how it goes. that is if the site ever goes live.