PDA

View Full Version : how to tell if a user has logged in?


rattlsnak
09-20-2008, 03:51 AM
I have a members only section on a website that I want to know when or if a user has logged in. Really, not so much when, as in time, although that would be nice., but I would like to see who has logged in at all actually and how many times.
Im using phpmyadmin with a mysql database table that has fields for id, username, password, and user level.
Im assuming i would have to open up the phpmyadmin console and query the database to get the info, but i dont know how to set up the part where it tracks the username or what kind of query to run.
I noticed my host allows me to see the IP addresses that were logged in, but of course i have no idea who they are!
I'm thinking i need to add another field in the database table that somehow counts this? or I read somewhere that I could use another table that has an id, username, and time field in it?
help!
Thanks.

edbr
09-20-2008, 05:16 AM
My thinking would be another table id,user name and a datetime field that on login, will a form will also enter a new record . then you could call from that table how many in total or by selected member name etc

rattlsnak
09-21-2008, 12:20 AM
OK, I have added two new fields to the same table that are 'login_time' with a DATETIME attribute, and 'login_number' with an INT attribute.

(I wasnt sure if I should have added a new table for this or not, but I put them into the present table.)

I dont need recent activity, or presently online stats for this site. Just a history of who has logged in and when.

What I dont know how to do , is to modify the login script to add both values i want.

Maybe an update table query?, but dont know how to go about that.

davidj
09-21-2008, 01:04 AM
straight after the login (successful) or welcome page do an insert into a log table

(new log table)

in the table have a date time stamp field type

where you populate this field in your SQL use a curdate() function and this will insert the time the user has logged on

example...


mysql_query("insert into table (field1, field2, field3)values('$var1', '$var2', CURDATE()));


you can then create reports from this data

rattlsnak
09-24-2008, 02:34 AM
David, I started out by doing this one step at a time. I made the new table, with a username (VARCHAR) and login_time (TIMESTAMP) fields. When I set up the variables and the INSERT query to insert the username and ran it first (before entering any time statements), it populated the username correctly, and also populated the login_time field as well. I didnt use an INSERT statement for the login_time field, it automatically did it. ( I didnt have to use the CURDATE() function)

i simply ran this: mysql_query("INSERT INTO table (username)VALUES('$username'))"; and it populated both fileds.

It worked, but is this an acceptable way of doing this? If so, thats all i really needed.

I also need to fix the timezone, and I'm looking into that as well.

edbr
09-24-2008, 02:48 AM
I just did something similar recently but although timestamp automatically updates, i found inserting into a date(time) field better to format the date on entry than after.

rattlsnak
09-24-2008, 02:55 AM
Ed, yes, thanks, this is what I just noticed: the attribute the TIMESTAMP auto assigned : ON UPDATE CURRENT_TIMESTAMP.

This is the result: '2008-09-23 18:22:12' which is fine with me!

I can see where you might not want to display it like that on a webpage, but in my case, it is not being displayed anywhere, i just want to know who has logged in and when. I will play around with the other way to see the differences and try to learn more! Thanks..

David, I forgot to mention, I did get confused by this: "in the table have a date time stamp field type". Are you saying a seperate field for ('date', and 'timestamp') or ('datetime') ? I think you meant, one field called 'date' with the type being 'timestamp' which is what I did. This being the case, no need for the CURDATE(), unless, I take the auto assign attribute away and then use the CURDATE() as you described.

rattlsnak
09-24-2008, 03:28 AM
Also trying to get it to display the correct time zone:
I cannot get the function to work:

date_default_timezone_set("America/New_York");

wont run the page when i add that.

what am i doing wrong this time? ;)

davidj
09-24-2008, 07:10 AM
ah you cant really use local times when recording timestamps

this is normally relative to the server time. As your not displaying this then its fine. You could write a function which calculates the difference depending on their location but id do this after its in the database.

you could capture the location on registration and store that location in the user table then use that again to feed the function which -/+ hours


switch ($result['local']){

case 'US': $diff = "-5"; break;
case 'AUS': $diff = "+8"; break;


}

so you would take the generic timestamp then use the switch to determine how many hours to adjust for local display