Page 1 of 1

Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 6:43 am
by simonmlewis
I have a site where a user logs in. This updates the "status" field to "online".
Users can then see that this users is online.

They must then Logout in order ot update the status to 'offline'.

The problem is, often these people are not Logging out, but closing the browser.
Further more, they do that assuming their 'session' will just end, and when they go to log back in, as the system thinks they are online, it won't let them in again (prevent multiple people using same login).

Is there a way to log people out after a certain time anyway....without browser interaction?

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 6:55 am
by Celauran
Are you storing sessions in the database? If so, you could set an expire time on them, which could be updated on new page loads.

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:02 am
by simonmlewis
The site isn't busy at the moment - it's in its infantcy.

When the user logs in, no there are no sessions. They just get their row updated as "online".
The customer sees there are "online" statuses for their contact.

I didn't know if there was a means to run a query directly on the server that would work, HOWEVER, if we had users abroad, then they would all get chucked out when UK people were sleeping.

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:12 am
by Celauran
Rather than having an online/offline field, you could create a field called login_expiry or somesuch. When a user logs in, this field is set to, say, 15 minutes in the future. Whenever they load a new page, that expiry is updated. No page loads, their login expires. No need for checking WHERE status = 'Online', you can just check WHERE login_expiry > NOW() to find out who is online.

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:19 am
by simonmlewis
Oh yes I think I see what you mean.
So when the customer goes to the site to see if anyone is "online", it's really checking to see if any operator user has an online time of anything greater (ahead of) the current server time.

But how would this prevent another user logging in as that user? Oh....maybe when someone logs in, if that operator time is greater than NOW, then it won't let them in, otherwise... it will.

mmmmmmmmmmmmm

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:21 am
by Celauran
Exactly. You could also record login IP, so if someone forgets to logout, closes their browser, and needs to get back in, they can. A second user trying to login to the account from another location would still not be permitted.

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:25 am
by simonmlewis
But how to you get the current TIME and then add say 5 mins to it, and then store that in a variable?

I did it for Date once, adding 7 days to it, but am sure doing it with minutes and seconds is more difficult.

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:27 am
by Celauran
Not difficult at all.

Code: Select all

$five_minutes_from_now = time() + (5 * 60);

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:28 am
by simonmlewis
Oh my life.... is it that easy????
How do you then ask if $now is less than or equal to $five_minutes_from_now?

I can see how that bit works tho. It gets the time(), then 5*60 is 5 minutes, and clearly the time() works on seconds and not minutes??

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:31 am
by Celauran
You could store time in the db as seconds since 1970-01-01 00:00:00 like the time() function does and use that in your comparison

Code: Select all

$now = time();
$query = "SELECT foo FROM bar WHERE login_expiry > {$now}";
or you could store it in the db as a DATETIME, use date() to format the time() string before inserting, and compare against NOW()

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:41 am
by simonmlewis

Code: Select all

$nowadd5 = time() + (5 * 60);
mysql_query("UPDATE admin SET status = '$nowadd5' WHERE email = '$email' AND password = '$password'");
This isn't doing anything. It just leaves the field as:

[text]0000-00-00 00:00:00[/text]

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 7:43 am
by simonmlewis
$nowadd5 echos: 1316695688

No wonder... do I need to convert it somehow??
use date() to format the time() string before inserting
Mmmm how do you do that?

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 8:04 am
by Celauran
STATUS is a reserved word in SQL. You'll need backticks around it.

Code: Select all

SET `status` = 'foo'
As for date()

Code: Select all

$datetime = date("Y-m-d H:i:s", time());

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 9:34 am
by simonmlewis
Ok here is a doozy of a problem to add to this.

We already wrote a script that lets the operator appear as "offline", but while they are offline, although the main window for customers shows they are offline so nothing else can be written by them, current users can still finish their conversations.

It's a means of allowing operators to end the rest of the conversations in a queue, before leaving. Bit like the "till closing" bar at the shops.

But.... my script that keeps them online is in the windows where they see conversations..... and while they are answering the rest, it keeps their status time up to date.

Bummer!

So how do I let them appear offline, while allowing them to finish conversations and NOT update that script??

Edited........... Unless you had two screens?
When they are inside the time period, it displays once screen that has the timeout script.
When they are changed their status, and it the timeout field becomes "123", it will display a different list screen seamlessley. It still does everything the other one does, but does NOT update their timeout?

Or is there a better way?

Re: Can a DB field be updated without user interaction?

Posted: Thu Sep 22, 2011 9:44 am
by simonmlewis
Bingo:

Code: Select all

$now = time();
$result = mysql_query ("SELECT status FROM admin WHERE company = '$website' AND id = '$cookieid' AND status != '01'");
$num_off = mysql_num_rows($result); 
if ($num_off == "1")
{
$nowadd5 = time() + (1 * 60);
mysql_query ("UPDATE admin SET status = '$nowadd5' WHERE id = '$cookieid'");
}