Jump to content

Storing / Using a Date


WinnieThePujols

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!
Link to comment
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]
Link to comment
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
Link to comment
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.
Link to comment
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.
Link to comment
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.)
Link to comment
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.
Link to comment
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...
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.