Page 1 of 1
Display date&time, storing users last login date&time
Posted: Wed Aug 27, 2008 9:55 am
by lynchpin
Hi,
I have a problem with displaying the current system date and time on my site, I also want to update my databse to store the date and time a user logs in. But when i used the date() function with PHP, it only stores the system date when I first load the page, and it doesnt change the next time.
Any suggestions please....
Thanks.
Re: Display date&time, storing users last login date&time
Posted: Wed Aug 27, 2008 10:09 am
by jaoudestudios
Show us what you have so far.
Re: Display date&time, storing users last login date&time
Posted: Thu Aug 28, 2008 3:21 am
by lynchpin
The lines that suppose to update the database goes something like this;
----------------------------------------------------
$now = date("Y-m-d h

i");
$query_string = "UPDATE members SET lastlogin= '$now' WHERE username='$username'";
$result = mysql_query($query_string, $connection);
----------------------------------------------------
It executes succesfully, the only problem is; after doing it the first time, the next time the user logs in, the same date and time is stored again.
Thanks again.
Re: Display date&time, storing users last login date&time
Posted: Thu Aug 28, 2008 10:48 am
by lynchpin
I dont understand why the above code doesnt work.
Can any one suggest another method please???
Thanks.
Re: Display date&time, storing users last login date&time
Posted: Thu Aug 28, 2008 11:10 am
by jaoudestudios
It should work, I cant see anything major wrong.
Try this, not sure if it will fix the problem but will be more efficient.
Code: Select all
$query_string = "UPDATE members SET lastlogin= '".date("Y-m-d h:m:i")."' WHERE username='".$username."'";
$result = mysql_query($query_string, $connection);
I recommend using time() instead of date(), as it makes it possible do date calculations.
Re: Display date&time, storing users last login date&time
Posted: Thu Aug 28, 2008 11:18 pm
by The_Anomaly
lynchpin wrote:The lines that suppose to update the database goes something like this;
----------------------------------------------------
$now = date("Y-m-d h

i");
$query_string = "UPDATE members SET lastlogin= '$now' WHERE username='$username'";
$result = mysql_query($query_string, $connection);
----------------------------------------------------
It executes succesfully, the only problem is; after doing it the first time, the next time the user logs in, the same date and time is stored again.
Thanks again.
Just throwing it out there, but could your DB lastlogin field type be giving you trouble? I mean, I always store my date/time as a unix timestamp, and as such, the field is set to an INT type. Storing a formatted date/time in an INT field would not work, obviously. Perhaps that's your problem?
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 3:42 am
by jaoudestudios
Ah yes good thinking.
What is your database structure and types?
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 4:30 am
by onion2k
The_Anomaly wrote:I always store my date/time as a unix timestamp
You shouldn't be doing that. Store date/time data in a datetime column, otherwise you're introducing a limitation that won't be in the specification (no dates beyond 2038)... that's bad software design.
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 6:07 am
by lynchpin
The type for the dblastlogin field is dateTime, but if this should work then its suppose to atleast update when I use it to display the current date and time on the page. The date displayed is always that of the initial variable, I use the following lines to display the current date and time;
--------------------------------------------
$now = date("D d-M-Y h:m a");
echo"Today: ". $now;
--------------------------------------------
Thanks
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 9:21 am
by The_Anomaly
onion2k wrote:The_Anomaly wrote:I always store my date/time as a unix timestamp
You shouldn't be doing that. Store date/time data in a datetime column, otherwise you're introducing a limitation that won't be in the specification (no dates beyond 2038)... that's bad software design.
Ah, I see. Somehow I thought that I was doing some thing wrong with that. So, I should be formatting the date as "YYYY-MM-DD HH:MM:SS" prior to inserting it into the Date field. However, is there a way to convert this format to a timestamp after selecting it? It's so much easier to manipulate the time as a timestamp--which why I was storing it as such in the beginning.
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 10:12 am
by onion2k
The_Anomaly wrote:onion2k wrote:The_Anomaly wrote:I always store my date/time as a unix timestamp
You shouldn't be doing that. Store date/time data in a datetime column, otherwise you're introducing a limitation that won't be in the specification (no dates beyond 2038)... that's bad software design.
Ah, I see. Somehow I thought that I was doing some thing wrong with that. So, I should be formatting the date as "YYYY-MM-DD HH:MM:SS" prior to inserting it into the Date field. However, is there a way to convert this format to a timestamp after selecting it? It's so much easier to manipulate the time as a timestamp--which why I was storing it as such in the beginning.
I believe if you insert a unix timestamp into a column that's a datetime type MySQL will automatically convert it, so there'd be no need to change your insert code unless you're going to be using dates outside of the unix timestamp range. If you are then yes, you'll need to format the input like you suggest.
To get a timestamp out of a datetime column use MySQL's UNIX_TIMESTAMP function, eg "SELECT UNIX_TIMESTAMP(`column`) AS column_unix FROM `table`". Better yet though, use MySQL's DATE_FORMAT() function. It's very like PHP's date() function. That way you're never resorting to timestamps and your code will handle any date. That's got to be good.
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 10:36 am
by jaoudestudios
Ah good thinking, thanks onion2k
Re: Display date&time, storing users last login date&time
Posted: Fri Aug 29, 2008 3:16 pm
by The_Anomaly
onion2k wrote:
I believe if you insert a unix timestamp into a column that's a datetime type MySQL will automatically convert it, so there'd be no need to change your insert code unless you're going to be using dates outside of the unix timestamp range. If you are then yes, you'll need to format the input like you suggest.
To get a timestamp out of a datetime column use MySQL's UNIX_TIMESTAMP function, eg "SELECT UNIX_TIMESTAMP(`column`) AS column_unix FROM `table`". Better yet though, use MySQL's DATE_FORMAT() function. It's very like PHP's date() function. That way you're never resorting to timestamps and your code will handle any date. That's got to be good.
Awesome. Thanks for the pointers. I'll do this.
Re: Display date&time, storing users last login date&time
Posted: Sat Aug 30, 2008 3:26 pm
by lynchpin
The time now only updates after every hour, i.e the initial time the file was created and every hour after that.
Please help, my database type for the lastlogin column is datetime.