Can a DB field be updated without user interaction?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
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?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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.
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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.
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?

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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.
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

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?

Post 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??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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()
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?

Post 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]
Love PHP. Love CSS. Love learning new tricks too.
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?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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());
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?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
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?

Post 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'");
}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply