Controlling Time

israfel

Or, at least dealing with it is what I'm having trouble with.

How can I efficiently measure the difference between timestamps (and which timestamping functions should I use?) and execute conditional code based on those results?

For instance, a pretty simple whoseonline would add usernames to a database table and "DELETE FROM users_online WHERE userid='$userid'" after about 15 minutes of inactivity but how do I do this?

It seems to me it's probably done with an expression that checks for a difference in seconds between $timea and $timeb and, if that time is greater then x, execute the delete query but my lack of knowledge in this particular point envelopes that entire area.

Can someone point me to a resource or supply code to allow me to get a current datetime, turn it into a readable datetime, and compare times in the abovementioned method. A table where I could find the number of seconds in 15 minutes or 1 day would also be useful.

Thanks.

[deleted]

sorry, i can't give you the code but here's the logic (your ideas a little bit more detailed):

make a columns in you db in which you store your timestamp.
everytime the page is laoded call a function that inserts the user in the db with the time of entrance (using PHP time() function).
and every time the page is loaded you selecet every user from the table in the db and check in a simple if statemant wheater the user is logn enough online or not:

$sql = "SELECT timestamp, user_name FROM table";

$result = mysql_query($sql, $link);
while($row = mysql_fetch_array($result)){
if((time() - $row["timestamp"]) > 5*60){
$user = $row["user"];
echo "User $user is blah bla bla for more than five minutes";
}
}

of course you can execute your sql command in the if clause, too.

hope that helped

ali

theslinky

Okay, first of all, how is your who's online table organized? Do you have a background program running to remove entries older than 15 minutes? Or are you deleting old entries as clients access your PHP?

This is the way I would do a who's online:

<?php

... setup db connection ...
$SQ = "DELETE FROM whosonline WHERE last_access < " . (time() - 60*15);
... call the query and pass in $SQ as the query ...

// some more code

// get the # of people online
$SQ = "SELECT COUNT(id) AS peopleonline FROM whosonline";
... execute query ...

So basically I first delete old entries and then run a COUNT() to get the # of people online within the past 15 mins.

-sridhar

israfel

My online table is (give or take a few columns) setup like this

onlineid int
username varchar
page varchar // page currently visiting
last_active int

So it should work with what you've given me Sridhar, thank you.

The question this leaves me with (which I guess doesn't merit it's own topic) is how the COUNT works. In the past I've been using mysql_num_rows with "SELECT id FROM table" queries to get numbers. I guess the only part of that query that doesn't make sense to me is the peopleonline part. Is that the name a variable is created with or something?

theslinky

Israfel,

COUNT() is a mysql function that counts the # of rows in a table (or as bounded by the where clause).

using count will be faster because no table data is returned: just the # of rows.

When you use mysql_num_rows, you are still executing the SELECT query which produces the data as a result, which is redundant because all you really need is the # of rows and not the whole data set.

As far as peopleonline, yes: it's a variable we create just at the time of the query. THink of it like a field name. You access it the exact same way as you access your other fields.

-sridhar

Anon

My two cents...

Never use mysql's date/time functions. Always let php handle the date/time use the time() function. Then you can manipulate it to your hearts content because it's based on seconds instead of having to make a function to convert a mysql timestamp to a useable php number of seconds deal. (I've done it both ways and letting php handle it is the absolute easiest).

Just my two cents...