[Brad2DBone]

When users on my site log out, I want to UPDATE the same table I use to TIMESTAMP their log-in. All users' log-in times are on the same table, with each user differentiated by a unique ID.

I was able to accomplish this, but my problem WAS that the wrong row in the MySQL database table was being updated for the user who logged out--the top row for the user (their first log-in record)was being updated, and I wanted to update their bottom row (their last log-in record).

To "fix" this issue, I implemented the following code, WHICH SEEMS TO WORK FINE. Does anyone foresee an issue with my syntax?

$userid = $_SESSION['user_id'];

	mysql_query("UPDATE login_timestamps SET id = id, ip = ip, timestamp = timestamp, logout = NOW() WHERE user_id=$userid ORDER BY `id` DESC LIMIT 1");

[JackOfAllTrades]

Wow, I just learned something. I would have never thought of ORDER BY and LIMIT in an UPDATE query, but it is supported. Fascinating. Perhaps this is a MySQL extension to the standard.

Personally, the better choice would be to store the ID of the login_timestamp record created at login in the session and use that as a means of updating the record you want.

EDIT: Behind the scenes I would bet that MySQL is doing the following:

SELECT id FROM login_timestamps WHERE user_id = $userid ORDER by id DESC LIMIT 1;

then

UPDATE login_timestamps set ip=ip, timestamp=timestamp, logout=NOW() WHERE id=id;

This post has been edited by JackOfAllTrades: 16 June 2012 - 01:09 PM

[e_i_pi]

You don't need to set all the values, just those you are updating:

UPDATE login_timestamps SET logout = NOW() WHERE user_id=$userid ORDER BY `id` DESC LIMIT 1

Your query will work fine though.

Are all the timestamps in your DB tables being calculated by MySQL, or are you sometimes passing FROM_UNIXTIME(time()) from PHP. If so, then you may fall into a trap where the MySQL is working on a different clock to PHP.