Run a query ever 2 hours automatically

justinede

Hello Community!

I need some help with a problem im having. When a user logs in, it adds a 1 to a row called "logged_in". Before it lets them in, it checks whats in that row first. If there is a 1, the user is denied because his account is already logged in. If there is a 0, it makes it a 1 then lets them in.

The problem is people are closing their browser and not hitting log out. So the session gets deleted on their computer but the database still thinks he is logged in.

So to fix this, i need something that will like run a MYSQL query automatically and go in and change all the 1s in logged_in to 0s every 2 hours.

Make a SQL statement altering the "logged_in" column when the user logs in. Set the default as 1.
"every 2 hours goes through and changes all the 1s to 0s" Set a timer at certain times to alter that field at specific times to go to a 0.

Thanks and Your Help is very much appreciated,
Justin

justinede

bump*

NogDog

I suppose you could set up a cron job if on a Linux/UNIX host or else via the Windows Scheduler, though if I were doing it I'd probably look into a refactoring of the login process so that it's not needed.

justinede

actually that wont work because of my host.
could you possibly help me make a script that will look at the time logged in and change the 1 to a 0 after 2 hours?

like a row called timein and it looks at that and two hours after that time it resets a different row's data?

here is wat i have.

i have a time stamp go in when they log out. so to make one when they log in, would i put the following code in this line?

time stamp code:
$time = time();
timeout = NOW()

that is in my logout.php
now to add it to my login
does it go in here?

$sql="SELECT logged_in FROM $tbl_name WHERE username='$myusername' and password='$mypassword' AND activated='1'";
$result=mysql_query($sql);

bretticus

Never done this so here's a hint...

You can define your own session functions. How 'bout using the session garbage collector to run your query?

See session_set_save_handler()

Note the gc() function in the example. You could extract the session id to be deleted and run a mysql query.

EDIT: Oh, keep in mind, you'd need to store the user's current session id in the database (shouldn't be a big deal if you have access to the database.)

justinede

thanks, but what i really need help on right now is how to have a script that looks at the time logged in and 2 hours from that time, it resets the 1 to a 0 in logged in. so it logs them out 2 hours after they have logged in.

NogDog

A query like this should do the trick, substituting appropriate table/column names

UPDATE table_name SET activated = 0 
WHERE activated = 1 AND DATE_SUB(CURDATE(), INTERVAL 2 HOUR) > timestamp_field

justinede

ok how do i make that query run automatically?

Weedpacket

Do it the same way PHP's session handler destroys expired sessions: as part of its session startup process. Everytime someone causes a session to start, update the db. As bretticus suggests.

If that sounds hit-and-miss, then consider what would happen with your "automatic" solution: If the update runs at 02:43 and someone abandons a session at 02:44, it won't yet be two hours old when the update next runs at 04:43 and will instead stay around until 06:43.

Oh, and you missed your 5:55 post.