List:General Discussion« Previous MessageNext Message »
From:Martin Jespersen Date:August 5 2006 12:34am
Subject:Re: FW: How do I find all the users that are new since my last login
(repost)
View as plain text  
You could add an extra field called last_login_date which you'd set only 
once per session - at login time. At login time you'd set this to the 
value that exists in login_date. Then use that for comparison against 
created_on.

Daevid Vincent wrote:
> I have a SQL challenge I'm not sure how to solve. But it's so common, I feel
> kind of stupid asking this...
> 
> I have a 'user' table with 'login_date' which is an auto updated DATETIME
> column and a 'created_on' which is a DATETIME (but not updated after the
> record is created the first time)
> 
> I want to show a list of users who are new since my last login. But the
> problem is that my last login changes for every page load (i.e. it is
> updated so that I can guestimate if a user is logged in still or not... I
> consider 10m to be the window, since rarely do users ever officially
> 'logout').
> 
> So I think I need to have a SQL query that only deals with the date, not the
> mins/secs? I'm just not sure the optimum way to do this.
> 
> The other challenging part seems to me that as soon as 'login_date' is
> updated, then everyone that was 'new' is now 'old' because the login_date
> just got updated right? I kinda want the 'new' user status to persist for
> the whole session [or maybe even the whole day (that is, I could logout/in
> and those people would still show as new) -- but I can live with just being
> new for the session]
> 
> How is this sort of thing usually handled? Do I need another column that
> isn't auto-updated and that just get's set upon each new 'login' session?
> Unlike a "message board" or web based "email" system, I CAN'T flag each user
> as viewed or something like that. This feature is for a "network" type
> scenario where a user can see new users added to their network of friends
> since the last time they logged in. Or so the Administrators can see all new
> users. This type of thing.
> 
> 
> ÐÆ5ÏÐ 
> 
> 
Thread
FW: How do I find all the users that are new since my last login (repost)Daevid Vincent5 Aug
Re: FW: How do I find all the users that are new since my last login(repost)Martin Jespersen5 Aug
  • RE: FW: How do I find all the users that are new since my last login (repost)Daevid Vincent5 Aug