Jump to content

Archived

This topic is now archived and is closed to further replies.

WinnieThePujols

Storing / Using a Date

Recommended Posts

I'm starting to get really frustrated, folks! Let me start by describing what I'm trying to do. All I want is to have my staff members input the birthdate of a baseball player, and then have it stored in a DATE field in a MySQL table entitled "player_info." I have other areas on the site (like the Articles section) in which I store dates and it works fine. The difference is that I use NOW() instead of a user-inputted date.

Now, to make sure they use the correct format, I broke the date down into three fields: month, date and year. So I combine those three variables when the form is processed, and create one new variable called "$birthdate." It's the the format YYYY-MM-DD.

So what do I have to do with $birthday before I insert it into the DATE column in the table? Because when I try and insert it the way I have now, it just shows up as 0000-00-00.

What do I need to change to get it to work?

And then when I pull it out from the database, what do I have to do to format it correctly? Do I need to use UNIX_Timestamp or something? One guy in particular told me to use that.

I want to be able to sort by birthdate when I query the database, but my current method is not working.

I cannot, for the life of me, find a turotial that handles this. Everyone I see uses NOW() and never uses a custom date. Please help -- it would be much-appreciated!

Share this post


Link to post
Share on other sites
I suggest storing ALL dates in an int field using unix timestamp. It makes it _much_ easier to manipulate the date at a later time. You might also want to check out [a href=\"http://us3.php.net/strtotime\" target=\"_blank\"]http://us3.php.net/strtotime[/a]

Share this post


Link to post
Share on other sites
[!--quoteo(post=365385:date=Apr 16 2006, 04:16 PM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 16 2006, 04:16 PM) [snapback]365385[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I suggest storing ALL dates in an int field using unix timestamp. It makes it _much_ easier to manipulate the date at a later time. You might also want to check out [a href=\"http://us3.php.net/strtotime\" target=\"_blank\"]http://us3.php.net/strtotime[/a]
[/quote]

OK: so instead of a DATE field, I make that INT? And then I just take the $birthdate variable and strtotime it?

See, I think that's what I'm using now. And then I use this query:

mysql_query("SELECT * FROM player_info ORDER BY info_birthdate DESC");

But then when I print out the $birthdate's in a loop, they aren't in order. How do I remedy this problem?

Eg: part of the loop looks like this:
Date: 02.11.72
Date: 01.21.72
Date: 08.15.81
Date: 10.01.80
Date: 09.09.79
Date: 08.23.79
Date: 05.23.79
Date: 03.31.79
Date: 03.03.79
Date: 02.24.79
Date: 02.24.79
Date: 08.14.78
Date: 06.10.78
Date: 04.18.77

Share this post


Link to post
Share on other sites
TIMESTAMP is fine for those born since 1970-01-01 but how are going to store birthdays for those like myself born a few years prior to that.

Also timestamps have the property of updating themselves when you update another column in the record, so if I update my address, my date of birth becomes today.

Share this post


Link to post
Share on other sites
I overlooked the birthday part of it, so you are correct on that. However, I was never telling the poster to use the MySQL 'TIMESTAMP' field, but rather an INT to store a unix timestamp. Also, if you were to use a TIMESTAMP field you can stop it from Auto-updating the field on change by setting a 'default' value with no 'on update' statement when creating the table.

Share this post


Link to post
Share on other sites
[!--quoteo(post=365391:date=Apr 16 2006, 03:34 PM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 16 2006, 03:34 PM) [snapback]365391[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Store the date in a unix timestamp.
[/quote]

Yeah, but I am, aren't I?

[code]
$birthday = strtotime($birthday);
      
       $add_player =mysql_query("INSERT INTO `player_info` ( `info_id` , `info_first` , `info_last` , `info_position` , `info_level` , `info_country` , `info_birthdate` , `info_height` , `info_weight` , `info_throws` , `info_bats` , `info_experience` , `info_number` )VALUES ('', '$f_first', '$f_last', '$f_position', '$f_level', '$f_country', $birthday, '$height', '$f_weight', '$f_throws', '$f_bats', '$f_experience', '$f_number')");[/code]

Isn't that storing it as a UNIX Timestamp? (The type of info_birthdate is just text, I think.)

Share this post


Link to post
Share on other sites
Well you pasted that when you were retrieving the data it was coming out as
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
Eg: part of the loop looks like this:
Date: 02.11.72
Date: 01.21.72
Date: 08.15.81
Date: 10.01.80
[/quote]
And that is not how a unix timestamp looks. Also, as Barand mentioned, which I overlooked, if the person was born before 1970, the unix timestamp won't work for them.

Share this post


Link to post
Share on other sites
[!--quoteo(post=365424:date=Apr 16 2006, 09:02 PM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 16 2006, 09:02 PM) [snapback]365424[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well you pasted that when you were retrieving the data it was coming out as

And that is not how a unix timestamp looks. Also, as Barand mentioned, which I overlooked, if the person was born before 1970, the unix timestamp won't work for them.
[/quote]

It's because before that loop, I did this:

$birthday = $testRow["info_birthdate"];
$bd = date("m.d.y", $birthday);
print $bd;

I'm just saying that I order by info_birthdate, the dates didn't come out in order...

Share this post


Link to post
Share on other sites
If you are using unix timestamp and the field is an int and you order by it, it WILL come out in order.

Share this post


Link to post
Share on other sites
[!--quoteo(post=365438:date=Apr 16 2006, 10:10 PM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 16 2006, 10:10 PM) [snapback]365438[/snapback][/div][div class=\'quotemain\'][!--quotec--]
If you are using unix timestamp and the field is an int and you order by it, it WILL come out in order.
[/quote]

OK, I have a text field instead of int. Could that be causing the problem, or don't you think that would throw it off?

I changed it to INT and it worked.

Thanks a lot for the help guys, I appreciate it.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.