Page 1 of 1

Monster of a SQL Query Problem

Posted: Mon Jul 16, 2007 9:37 am
by mad_phpq
Ok. I have this query to return all user logins from a transaction_log. Is it possible to get the last login from the log and update the users table to set the account as inactive ie active =0

Thanks to volka i got this to return the last login time for all users. Now, how do i update the users table from this last login time.

Code: Select all

SELECT
  a.user_id,u.username,u.email,u.firstname,u.surname,u.active, a.logtime
FROM
  transaction_log AS a
LEFT JOIN
  transaction_log AS b
ON
  a.user_id=b.user_id
  AND a.logtime<b.logtime
JOIN
  users AS u
ON
  u.id=a.user_id
WHERE
  b.logtime IS NULL
  AND u.guest = 1 

Posted: Mon Jul 16, 2007 11:22 am
by Begby
Well you have everything that you need, now you just need to loop through your result set and just run an update for each record where lastlogin date is a certain age.

pseudo code

Code: Select all

for each of the user records
{
  if the login time is older than x days, then :
    update the user table and set active to 0
}

Posted: Mon Jul 16, 2007 10:25 pm
by feyd
The original question was asked in previous thread. Locked.

viewtopic.php?p=400837&highlight=#400837