Hello! Please help with the request. There is a request

$this->db->query("SELECT users.* FROM users JOIN sessions ON users.nickname=sessions.nickname WHERE sessions.last_activity > ".(time()-180));

and I need to make a similar query just not a SELECT but UPDATE, something of type

 $this->db->query("UPDATE users SET users.activity='Online' JOIN sessions ON users.nickname=sessions.nickname WHERE sessions.last_activity>".(time()-180));

    2 answers 2

     UPDATE users, sessions SET user.activity = 'Online' WHERE user.nickname=session.nickname AND sessions.last_activity>".(time()-180)."

    PS Are you sure you should use nickname? If this field does not have an index in at least one table, the query will work for a very long time. Usually, the userid field or the like with the type int and primary index on it is used.

    • primary index - this is probably the primary key for the field? I made such a request, since information about the session and the user's time is stored in one table, and the output of user data comes from another table, probably I should have done differently. For example, track user actions through JS, compare them with the current session and update the data immediately in the users table and do not make such confusing queries. It is still not entirely clear how to track through the sessions (standard php) the time of the last request to the server from a specific user - sew810i9
    • If there is a task to track the exact time of the last access to the site, then you will have to send UPDATE session SET activity = NOW () with each call. It is not clear at what point you are going to do the UPDATE specified in the question initially. It's easier when outputting a joint (LEFT JOIN) session and output "online" if the last session of the user was registered no later than time-180 - Anton Smurov

    Replace join with a subquery: update users set activity='Online' where nickname in (select nickname from sessions where sessions.last_activity>".(time()-180)));

    • Zufir, did not quite understand, so what ("SELECT users. * FROM users UPDATE users set activity = 'Online' where nickname in (select nickname from sessions where sessions.last_activity>". (Time () - 180))); - sew810i9
    • one
      and select why? - Zufir
    • Zufir, ("UPDATE users SET activity = 'Online' WHERE nickname in (select nickname from sessions where sessions.last_activity>". (Time () - 180))); it turns out the bracket is superfluous, in the end, if you remove it, a syntax error takes off. Please correct, where I wrote incorrectly - sew810i9
    • one
      This is PHP? Then, probably, like this: $this->db->query("UPDATE users SET activity='Online' WHERE nickname in (select nickname from sessions where sessions.last_activity>".(time()-180).")"); But in PHP, I almost did not write, so I remember very little by syntax :) - Zufir
    • Zufir, thank you! You are probably not a php fan? On something else write applications? - sew810i9 pm