PHP Forum

**dansgalaxy** · 11-03-2007, 03:48 PM

okay.

im trying to have a last action on each page load which saves the unix time to the field for the logged in user.

i also have it so if A (ANY USER, on ANY computer) opens a page it also checks the database for any users which their status says online but they not done any new actions for more than 3 hours and changes them to offline, sets the last_logoff_date to the current time.

But i dont this, and it appreard to work, i havent been able to test the change back bit yet but it dont seem to be updateing the unix time stamp for my last action on each page load. AND the stamp is much smaller than exected.

and in fact i just check and it appears to have set the time stamp for 04 / 07 / 70 @ 9:10pm it hasnt changed this time stamp since i first run the script

<STRONG><FONT face="Courier New">

PHP Code:


			
function update_user_sessions() {
$date = date('U');
$user_id = $_SESSION['user']['id'];
mysql_query("UPDATE users SET last_action='$date' WHERE id='$user_id'");

$max_last_action = $date - 60*60*3;
$update_res = mysql_query("UPDATE users SET status='offline', last_logoff_date='$date' WHERE status='online' AND last_action < '$max_last_action'");

} //End Function

thats the function and it is called in my header scripts on every page.

no errors are shown.

So why isnt it updating?

is there a obvious error im missing?!

Thanks,
Dan

**frost** · 11-03-2007, 06:05 PM

PHP Code:


			
$user_id = $_SESSION['user']['id'];

Is the correct user id being assigned from that line? If the user id is incorrect, nothing will change in the table and no errors will be thrown when the UPDATE query is run.

**mgraphic** · 11-04-2007, 05:55 PM

I think the line: last_logoff_date='$date' is voiding the query because it probably does not find a value for last_log_date to equal exactly as $date

**dansgalaxy** · 11-05-2007, 11:09 AM

Sorry huh?! @ mgraphic
no clue what u mean

**TwistMyArm** · 11-06-2007, 08:37 AM

What data type are you using for your 'last_action' and 'last_logoff_date' fields in your database?

Depending on what the type is for those columns, you may need to convert the dates from a UNIX timestamp to a MySQL date to use them properly. To do that, simply wrap your date values with a 'FROM_UNIXTIME()' function call in the query.

ie. change:
mysql_query("UPDATE users SET last_action='$date' WHERE id='$user_id'");
to:
mysql_query("UPDATE users SET last_action=FROM_UNIXTIME('$date') WHERE id='$user_id'");

and change:
mysql_query("UPDATE users SET status='offline', last_logoff_date='$date' WHERE status='online' AND last_action < '$max_last_action'");
to:
mysql_query("UPDATE users SET status='offline', last_logoff_date=FROM_UNIXTIME('$date') WHERE status='online' AND last_action < FROM_UNIXTIME('$max_last_action')");

Who knows. That date/time you gave appears to be the UNIX epoch. If I were you, I'd do two things if the above didn't work:
1) print the $date variable to screen, just to confirm that it's getting something 'useful' and that that 'useful' value is changing each time you load it.
2) in your MySQL database, change your 'last_action' and 'last_logoff_date' values to something that you know, then run the script again. If the numbers are changed back, at least you know that the query is doing SOMETHING.

Might I also just do a little bit of nitpicking? Using strings to represent state is not great (unless of course that string is being used on an enum column). Also, storing the 'last_logout_date' and the 'status' seems to be wasting space. In reality, instead of every page load changing someone's status depending on how long it's been since their last action, and then apparently using that status to determine if they are online or not, why not just look at their last action date when trying to determine their status? That way you don't need to store 'status', you don't need to store 'last_logoff_date' and you don't need to be updating those columns on each page load, either...

**dansgalaxy** · 11-06-2007, 11:55 AM

okay i think i get what you mean but i dont really understand the whole mysql time issue with unix time, which i prefer, why does it have trouble?

i know the online and everything isnt necassart but to be honest im being lazy, it juts makes it a little bit easier for me.

**TwistMyArm** · 11-06-2007, 01:47 PM

It's just the way it is: one has a different idea of time to the other.

That's only the case, though, if you are using standard MYSQL time/date data types. If you use timestamp, I think it should work 'out of the box'. That's a big 'I think', though. It's been a long time since I've used UNIX / MySQL time types directly (I got sick of it all and wrote a couple of functions myself).

**dansgalaxy** · 11-06-2007, 01:49 PM

what did the fuctions do? o.0

**TwistMyArm** · 11-07-2007, 09:10 AM

Nothing major, really.

A lot of the coding I do these days is in Joomla so I just change the read and write functions of my table objects so that straight after the row is read from the database it is converted from a MySQL datetime to a UNIX timestamp. The reverse happens just before it is written to the database.

I do it that way so that I can use UNIX timestamps in PHP, store the data as a MySQL datetime and yet not have to actually touch any of the 'real' read or write code. Plus it lets me more easily port the code to another database if need be.

But like I say, the functions themselves are not really 'hardcore' by any stretch: they basically just do exactly what the MySQL functions would do.

**dansgalaxy** · 11-07-2007, 11:39 AM

is there like no way you can disable the mysql default so it treats the numbers as php would (and everyone else) like with php u can change the ini settings?

also could u give a example of Mysql time?

**TwistMyArm** · 11-07-2007, 12:53 PM

Quote:

Originally Posted by dansgalaxy

is there like no way you can disable the mysql default so it treats the numbers as php would (and everyone else) like with php u can change the ini settings?

No, you can't. One major reason is that there's nothing stopping you just storing the UNIX timestamp in a massive int, for example. Without you telling us what data types you are using, there's not much else we can help you with there.

The thing is, you are presuming that PHP does it "right" by using a massive number to represent time and that MySQL does it "wrong" by using a proper date string to represent the date. Neither are right or wrong, they're just different. You have to learn to accept that.

You may as well be asking "is there any way I can get the Japanese to say their numbers the same way as the Welsh (and everyone else)". Hopefully you can see the problem there!

My point? Just because the two don't agree on how to represent date and time doesn't make one right and one wrong: you just have to deal with it.

Quote:

Originally Posted by dansgalaxy

also could u give a example of Mysql time?

I can't give you any better than http://dev.mysql.com/doc/refman/5.1/...-overview.html .

**maxxximus** · 11-07-2007, 03:47 PM

ON UPDATE CURRENT_TIMESTAMP might be useful to you here.

Add a new column to users table and give it a timestamp data type with the above attribute.

Whenever row is updated (status to online perhaps) then it auto updates date(non-unix).

**dansgalaxy** · 11-07-2007, 04:27 PM

well no i just got told that like it messes up like when i want to do *unix time stamp 1* < *cur unix time stamp* in like a where on a mysql thing thats all i really want to do i just dont understand why it dont just understand it as a number like.