From: Daevid Vincent
Date: August 4 2006 11:43pm
Subject: FW: How do I find all the users that are new since my last login (repost)
List-Archive: http://lists.mysql.com/mysql/200665
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.


=D0=C65=CF=D0=20