List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 5 2006 12:41am
Subject:RE: FW: How do I find all the users that are new since my last login (repost)
View as plain text  
I've thought about this a bit more since last night, and it seems that I can
just use the built in 'CURRENT_DATE' (sp?) as it's a given that if they're
logged in, then they're logged in NOW() which is == CURRENT_DATE minus the
hh:mm:ss.

I guess I could also store in a session (this is in PHP) the 'login_date'
BEFORE I update it with the current info. So effectively, that would be the
LAST login date right?

Then I need to whip up some SQL to compare all users created_on date BETWEEN
that 'LAST' date and CURRENT_DATE?

DÆVID  

> -----Original Message-----
> From: Martin Jespersen [mailto:mbj@stripped] 
> Sent: Friday, August 04, 2006 5:35 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: FW: How do I find all the users that are new 
> since my last login (repost)
> 
> 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