Display date&time, storing users last login date&time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lynchpin
Forum Commoner
Posts: 60
Joined: Mon Jul 21, 2008 1:31 pm

Display date&time, storing users last login date&time

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display date&time, storing users last login date&time

Post by jaoudestudios »

Show us what you have so far.
lynchpin
Forum Commoner
Posts: 60
Joined: Mon Jul 21, 2008 1:31 pm

Re: Display date&time, storing users last login date&time

Post by lynchpin »

The lines that suppose to update the database goes something like this;

----------------------------------------------------
$now = date("Y-m-d h:m: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.
lynchpin
Forum Commoner
Posts: 60
Joined: Mon Jul 21, 2008 1:31 pm

Re: Display date&time, storing users last login date&time

Post by lynchpin »

I dont understand why the above code doesnt work.

Can any one suggest another method please???

Thanks.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display date&time, storing users last login date&time

Post 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.
User avatar
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

Re: Display date&time, storing users last login date&time

Post by The_Anomaly »

lynchpin wrote:The lines that suppose to update the database goes something like this;

----------------------------------------------------
$now = date("Y-m-d h:m: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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display date&time, storing users last login date&time

Post by jaoudestudios »

Ah yes good thinking.

What is your database structure and types?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Display date&time, storing users last login date&time

Post 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.
lynchpin
Forum Commoner
Posts: 60
Joined: Mon Jul 21, 2008 1:31 pm

Re: Display date&time, storing users last login date&time

Post 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
User avatar
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

Re: Display date&time, storing users last login date&time

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Display date&time, storing users last login date&time

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display date&time, storing users last login date&time

Post by jaoudestudios »

Ah good thinking, thanks onion2k
User avatar
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

Re: Display date&time, storing users last login date&time

Post 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.
lynchpin
Forum Commoner
Posts: 60
Joined: Mon Jul 21, 2008 1:31 pm

Re: Display date&time, storing users last login date&time

Post 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.
Post Reply