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:[EMAIL PROTECTED] > Sent: Friday, August 04, 2006 5:35 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > 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ÏÐ > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
