PHP/MySQL Update database on session timeout

Question

I'm working on a "flash website". In this program, I'd like to add an "Online Users" list, which relies on sessions: A session is started when a user logs in, and the user is marked as Online in the database. As soon as the logs out or closes the browser, the user is marked as Offline in the database.

I know that running some functions when the browser is closed will require Javascript, and it's not safe either: If the browser were to crash, the functions wouldn't run. That's why I've settled for the database updating if the user logs out or if the user's session times out.

I've been looking up session timeouts, and ran into this, along with many others like it marked as duplicates: How do I expire a PHP session after 30 minutes?

The problem with the answer's method is; It's a conditional sentence that checks if the user's last activity was X seconds ago, and if there was no activity, it times out. Useful in some websites, but useless for the Online Users list, since it updateds when a new request is sent, and since there won't be any requests after the user closes the browser.

Also mentioned in other answers is the use of session.gc_maxlifetime and session.cookie_lifetime, but the Best answer states that using them is a bad idea; One doesn't destroy the session, just the cookies, and the other is "cost-intensive".

What I want is the user to time out and the database to update and mark the user as Offline without using the If-Conditional sentence, or maybe with using a different If-conditional sentence that only has to be used once when the user logs in, like a timer or something, and whenever a request is sent, the timer restarts...those are just my ideas so far on how to solve this problem.

But, how do I do this? I'm sorry if the answer is something very simple and obvious, I'm very new to PHP.

Edit: Long Story Short:

I want to run a function after the user closes his browser, or is inactive for 20 minutes.

Clarification...

I want to update the database after 20 minutes of inactivity even if the browser has been closed.

score 2 · Accepted Answer · answered Jul 15 '14 at 19:38

2

I want to update the database after 20 minutes of the browser being closed.

I don't think this is really possible to do with browsers being closed (reliably). If they click the logout button you can obviously run some code to mark the user as logged out. You can specify how long a session is good for with the functions you mentioned but all this does is makes the cookie be deleted on the client side, it doesn't trigger anything on the server side.

About the only thing you can do is track the time of the user's last activity and if they haven't had activity in a while assume they are gone. So just add a field to your user table and store a datetime or unix timestamp of their last activity. Then you can either run a cron job every few minutes that marks users as logged out or you can simply modify your SQL query to pull only users that have a last activity less than 20 minutes ago.

answered Jul 15 '14 at 19:38

Pitchinnate

7,517
1
20
37

I updated the question for more clarification; I want to update the database after 20 minutes of inactivity **even if** the browser has been closed. Is there a way, maybe, for the server or database to launch functions on its own? PS: Thanks very much for your answer! If there are no other solutions I'll use your workaround, I didn't think of only pulling users with a specific activity time. – MNOPYZ Jul 15 '14 at 20:29
As I said in my answer you could have a cron job that runs every few minutes that calls some php code. – Pitchinnate Jul 15 '14 at 21:01
Ah, didn't understand that part, I thought it was a typo. So, what's a cron job? xD – MNOPYZ Jul 15 '14 at 21:44
On a linux server a cron job is simply a scheduler, you can setup a cron job that runs every minute, every 5 minutes, once a day, once a month, etc... If you do a simple google search you will find much more info. – Pitchinnate Jul 16 '14 at 13:27

score 2 · Answer 2 · answered Jul 17 '14 at 07:10

2

You can do this with the help of a online users table with last active field,

window.setInterval(function() {
    $.ajax({      
      url: "http://yourdomain/updateLastactive/?uid=" + User_ID,
      success: function(data) {

      }
    }); 
}, 5000); // 5 seconds

This page will update the current time-stamp as last active for that user

Then on online users query you can do like below,

SELECT UID FROM ONLINE_USERS WHERE LAST_ACTIVE >  DATE_SUB(NOW(), INTERVAL 30 MINUTE));

answered Jul 17 '14 at 07:10

JSunny

477
2
7

Sorry for asking such a noobful question, but...where exactly do I put that first part? I haven't got any experience with Java/Ajax whatsoever. – MNOPYZ Jul 17 '14 at 11:40
That you need to write inside the [document ready](http://api.jquery.com/ready/) function like below – JSunny Jul 17 '14 at 12:05
Just to make sure, does the only thing the Javascript part do is update the Last Activity time for that user? – MNOPYZ Jul 17 '14 at 12:45
Yes, JavaScript is using only for calling the update database for last activity. – JSunny Jul 17 '14 at 13:02
Well, thanks alot for your answer, but there's another answer that suggests a way to do it using only mysql and php. I'm going to accept that answer, but I'll give you the bounty for your effort. Thanks again! ^^ – MNOPYZ Jul 17 '14 at 13:29

score 1 · Answer 3 · answered Jul 12 '14 at 18:11

1

if (!isset($_SESSION['CREATED'])) {
    $_SESSION['CREATED'] = time();
} else if (time() - $_SESSION['CREATED'] > 1800) {
    // session started more than 30 minutes ago
    session_regenerate_id(true);    // change session ID for the current session an invalidate old session ID
    $_SESSION['CREATED'] = time();  // update creation time
}

Also read this http://php.net/manual/en/session.configuration.php#ini.session.gc-maxlifetime

answered Jul 12 '14 at 18:11

Martney Acha

2,802
4
33
48

That's the same If-conditional I've been talking about and linked to :/ Do you mean that even if I don't use this if Conditional in every request, just the Login, it'll timeout after 30 mins? – MNOPYZ Jul 12 '14 at 20:32
You can modified this script based on your needs, inside the conditon. – Martney Acha Jul 15 '14 at 06:45
Even if I modified it, it still wouldn't run after the browser is closed. My question is, how do I make the session time out after X minutes **without** having to run a new conditional after X minutes. – MNOPYZ Jul 15 '14 at 11:36

score 1 · Answer 4 · edited May 23 '17 at 12:26

You want to catch the event by using the information here: How to capture the browser window close event?

You will then need a page on the server that you call in that event handler with the sessionID.

That page calls a local script, page, or service that you set up to delay 20 minutes and expire the session. If you want to restart the clock if the user revisites before expiration, you will need to include a way to interrupt or cancel that script's process and you will need to retain the SessionID in a cookie which will need to be looked for upon every page load. If the cookie's SessionID is found and matches, the timeout gets interrupted/stopped (if it's still going). If it already completed, a new SessionID will need to be issued.

If you have a lot of these cancellations at the same time, you will instead queue them up on the server and have a process periodically check the queue and expire them as needed.

The way most sites avoid all this is to set a 30 minute or whatever session timeout that is enforced whether the visitor is active or not.

Pretty nice solution, but like I mentioned, it most likely won't work if the browser crashes. That last line in your answer, that's what my question is all about, I want to know how to do that, but I can't use the function mentioned in the link because I won't be able to run it after the page closes...So, how do I set a "30 minute session"? — MNOPYZ, Jul 15 '14 at 19:42
There is another answer here for the plain session timeout. That will run when the page is reopened, but not if the browser crashes and the page isn't reopened. The only reason I can think of to track that is if you are tracking active session counts or something. The best way to do that would be to store the timestamp and sessionid in a table on the server and have a script run periodically to update or remove the timed out rows. — DTecMeister, Jul 16 '14 at 05:39

score 0 · Answer 5 · answered Jul 16 '14 at 00:14

The easiest way to do this is to write a MySQL stored procedure that looks at the session timestamp and deletes the session data when it is > 20min old. You can also do it as an external cron job, but a stored procedure would be better.

In addition, you need to refresh the session timestamp every time the user connects.

If you want to make sure you don't delete a session when the user still has your 'page' open, then some background JavaScript refresh to your site will function as a keep-alive. Note that if the user closes the 'window' or 'tab', it will be the same as the whole browser being closed - I'm not sure you can do anything about this.

Of course, you to do this, you need to store your sessions in MySQL.

TL;DR

MySQL stored procedure or cron job deletes session if session_timestamp > 20min
Client-side Javascript does ajax refreshes to keep session open
Session timestamp updated every time the 'page' is requested (browser or ajax).

PHP/MySQL Update database on session timeout

5 Answers5