Storing / Using a Date

[WinnieThePujols]

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!

[ypirc]

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]

[WinnieThePujols]

[!--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

[ypirc]

Store the date in a unix timestamp.

[Barand]

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.

[ypirc]

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.

[WinnieThePujols]

[!--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.)

[ypirc]

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.

[WinnieThePujols]

[!--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...

[ypirc]

If you are using unix timestamp and the field is an int and you order by it, it WILL come out in order.

[WinnieThePujols]

[!--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.