Hard to explain, has to do with users, last logged intime, and my database

[kenji776]

Okay everyone this is closly related to my other topic but they are different.
I keep a field in my database called loggedin, which is set to yes if they are
logged in, and no if they are not. However as well all know it is rather
difficult to say who really is or is not logged in because users usually close
the web browser without logging out. So what i decided was to make a field
that records the last time they logged in, then have my index page check to see
if they logged in more than one half hour ago. If so, then log them out. I know
ill be using the datediff function. I just can't seem to put it together in my
brain, how do I write a query that updates users, that says if lastlogged was
more than a half hour ago, set loggedin to no?

[mxstu]

I don't know what db you are using, so I will use access for this example.

Assuming "loggedin" is a YES/NO field, when the user logs in you set
"loggedin" to YES and the "loggedInDate" to the current date and time. To log
users out, just find all records where "loggedin" is YES and the "loggedInDate"
is more than thirty minutes ago OR the "loggedInDate" is NULL (i.e. something
is wrong if there is no login date).



<!--- something like this ...--->
<cfquery name="loginUser" datasource="#DSN#">
UPDATE yourTable
SET LoggedIn = YES,
LoggedInDate = #CreateODBCDateTime(rightNow)#
WHERE userID = #currentUserID#
</cfquery>

<cfset thirtyMinutesAgo = DateAdd("n", -30, now())>
<cfquery name="logoutUsers" datasource="#DSN#">
UPDATE yourTable
SET LoggedIn = NO,
LoggedInDate = NULL
WHERE LoggedIn = YES AND (
LoggedInDate < #CreateODBCDateTime(thirtyMinutesAgo)# OR
LoggedInDate IS NULL
)
</cfquery>

[kenji776]

I am using MS access, I know its crappy and i should be using oracle, but
whatever. I am not familiar with
#CreateODBCDateTime(rightNow)#
is that like the now() function? Thanks a bunch for the code, that helps quite
a bit, I was having like a logical breakdown and I could not for the life of me
figure out how to write that query. Thank again!

[mxstu]

Yes, it's awful when your brain hits a temporary road block!

Oops. I forgot to include the line where I set the variable named "rightNow".
Yes, it's basically another way of saying SET ColumnName = #Now()# ... (i.e.
the current CF date and time).

[mxstu]

Btw, you could really condense these two statements into one line. I split it
out for clarity...

<cfset thirtyMinutesAgo = DateAdd("n", -30, now())>
......
LoggedInDate < #CreateODBCDateTime(thirtyMinutesAgo)# OR
.......


condensed:
..... LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now())) .....

[kenji776]

Alright cool thanks, so that code should work with MS access? Im just curious
so I know if I should be expecting errors when I go to paste it in or not, I
can' t test right now cause im at a friends house just checking these forums ;)

[kenji776]

wow that is an ugly looking line of code
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))
haha I don't even know what the hell that is! Funny to me for some reason.

[mxstu]

It worked fine for me with Access 2000. Just make sure
A) LoggedIn is type "YES/NO"
B) LoggedInDate is type "date/time" AND
C) You replace the line: #CreateODBCDateTime(rightNow)# with: #Now()#

[kenji776]

oh well those could be slight problems,
yes/no is a string,
and last_logged_in is a string also.
Is there an easy way to make you query work with that? I would change the
database, but some other things use the database and know those vars as strings.

[mxstu]

Originally posted by: kenji776
wow that is an ugly looking line of code
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))
haha I don't even know what the hell that is! Funny to me for some reason.

Don't knock it till you've tried it ;-) You should use the one that seems
more clear, that way you won't have trouble with it 6 months down the road.

In a nutshell ...

1. Get the time now
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))

2. Deduct thirty minutes from it
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))

3. Now convert it into a date and time format Access can understand
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))

ie. format {ts '2005-07-28 23:44:02'}

4. Now find all users that logged in more than 30 minutes ago
LoggedInDate < CreateODBCDateTime(DateAdd("n", -30, now()))

[mxstu]

Is there an easy way to make you query work with that?

Changing the YES/NO field to a "text" is pretty simple. IMO using YES/NO is
better but the change would not be difficult.

I would recommend changing the logged in date to "date/time". Storing dates
as strings is just asking for trouble.

[kenji776]

your probobly right, i guess il make the change and deal with the errors as they come from the other pages.

[mxstu]

What else are you using the field for ... and how do you insert the date/time into that field? ex. using #Now()#?

[kenji776]

I guess I don't know for a fact if it will cause errors, its just that I havn't
used types of fields very often, and im not really sure sometimes how to deal
with them, because i often get database errors. the only time i use the
last_logged_in field is only used when initially logging in, and when people
view that persons profile. You can check it out at my site
[url]www.digitalswordsmen.com[/url]. I guess it shouldn't be bad to make the swap, just
hadn't really thought about it much. You should go and make an account on my
site ;) you seem like a cool guy. But yeah... ill change it tommorow or so, if
i have time.

[mxstu]

I'll take a look.

Well, it is handled differently than a text field, but once you get the hang
of it, it's easy. Since you're only using it in two places the effect
shouldn't be so bad. The one thing you will probably need to change is how you
display the logged in date. Once the column value is a date/time, when you
CFOUTPUT the values they will look something like this:

2005-07-28 23:44:02

You will probably need to use the DateFormat() function to format them the way
you want.

[kenji776]

alright sounds good. Thanks for all the help man, your awsome. Ill post back if i have any probs, don't know when ill have time to test it, sunday probobly cause im going to my cabin for the weekend.

[mxstu]

Good luck. Let me know how it goes!