How to update user last record

I want to know roughly how long do registered users browse my website,
Every login is registered.
I want to update logout time every page load / reload (and logout)
Here is a screenshot of the table

And here is the query’s last version:

			 SET logout_t = NOW()
			 WHERE user_id = 30
			 And l_id = MAX(l_id)

This specific query gave an error message: #1111 - Invalid use of group function
I suppose I can’t use Max()

What is the correct form to update a user last logir record ?
Other version gave error mesages or, as you can see - updated all user records

MAX() is an aggerate function and should/must (depending on your database server’s strict mode setting) be used with a GROUP BY term in a SELECT (data retrieval) query.

You can use a SELCT sub-query to do this -

	SET logout_t = NOW()
	WHERE l_id = (SELECT MAX(l_id) FROM logs WHERE user_id = 30 GROUP BY l_id)

If you only want to do this for a logout_t value that hasn’t already been set, you would add AND logout_t IS NULL to the main query’s WHERE clause.

not quite ;o)

if you group by l_id, the MAX of that l_id will be l_id

just remove the GROUP BY clause altogether

1 Like

You can also try an ORDER BY and LIMIT clause to update only the first row that matches the user_id in descending order of l_id.

Edit: Oops, the GROUP BY should have been GROUP BY user_id (to prevent a query error in strict sql mode.)

since there’s only one user, you can drop the GROUP BY clause altogether

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.