Can a DB field be updated without user interaction?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Can a DB field be updated without user interaction?
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?
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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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
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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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.
I did it for Date once, adding 7 days to it, but am sure doing it with minutes and seconds is more difficult.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
Not difficult at all.
Code: Select all
$five_minutes_from_now = time() + (5 * 60);-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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??
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??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
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
or you could store it in the db as a DATETIME, use date() to format the time() string before inserting, and compare against NOW()
Code: Select all
$now = time();
$query = "SELECT foo FROM bar WHERE login_expiry > {$now}";-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
Code: Select all
$nowadd5 = time() + (5 * 60);
mysql_query("UPDATE admin SET status = '$nowadd5' WHERE email = '$email' AND password = '$password'");[text]0000-00-00 00:00:00[/text]
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
$nowadd5 echos: 1316695688
No wonder... do I need to convert it somehow??
No wonder... do I need to convert it somehow??
Mmmm how do you do that?use date() to format the time() string before inserting
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can a DB field be updated without user interaction?
STATUS is a reserved word in SQL. You'll need backticks around it.
As for date()
Code: Select all
SET `status` = 'foo'Code: Select all
$datetime = date("Y-m-d H:i:s", time());-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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?
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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Can a DB field be updated without user interaction?
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'");
}Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.