Monster of a SQL Query Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Locked
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Monster of a SQL Query Problem

Post 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 
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The original question was asked in previous thread. Locked.

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