How to update user last record

[erezvol]

Hello,
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:

UPDATE logs
			 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

[mabismad]

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 -

UPDATE logs
	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.

[r937]

@mabismad

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

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

[lara]

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.

[mabismad]

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

[r937]

@mabismad

the GROUP BY should have been GROUP BY user_id

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