Reputation: 3223
What is the best way to store user BIRTHDATES in MySQL/PHP and why?
I'm currently storing it as a VARCHAR(10)
in the following format:
MM-DD-YYYY
...but I have a feeling this isn't the best way, especially since I sometimes see invalid years in that field.
Please don't just say what the best method is, also explain why it's the best method so that I can be convinced to change the entire way birthdates are handled on my site, which is a huge undertaking should that be needed.
Thanks.
Upvotes: 0
Views: 464
Reputation: 3
This Is the php part with verification do declare the Err variable to null before.
<?php
if (! isset ( $_POST ['yearOfBirth'] ) || empty ( $_POST ['yearOfBirth'] )) {
$isFormValid = FALSE;
$yearErr = "Please select year";
if (! isset ( $_POST ['monthOfBirth'] ) || empty ( $_POST ['monthOfBirth'] )) {
$isFormValid = FALSE;
$monthErr = "Please select month";
if (! isset ( $_POST ['dayOfBirth'] ) || empty ( $_POST ['dayOfBirth'] )) {
$isFormValid = FALSE;
$dateErr = "Please complete the dob";
$dob = $_POST ['yearOfBirth'] . "-" . $_POST ['monthOfBirth'] . "-" . $_POST ['dayOfBirth'];
// exit(print_r($_POST));
}
}
}
---------------------------
Here is the SQL part.
----------------------------------
$dob = $_POST["yearOfBirth"] . "-" . $_POST["monthOfBirth"] . "-" . $_POST["dayOfBirth"];
$register = "INSERT INTO tablename( dob, )
VALUES('$dob',)";
Hope this helps
Upvotes: 0
Reputation: 10711
As others have already answered: MySQL has a DATE format for just dates, but if you need a time and date there's also DATETIME and TIMESTAMP.
DATE requires 3 bytes of storage, DATETIME require 8 bytes, and TIMESTAMP requires 4.
Incidentally, INT also requires 4 bytes. If you're just using a DATE without a corresponding time - like for your birthdays - then it's not really an issue, however there is an article which presents some arguments as to why you might want to avoid using DATETIME, and I think it's worth a read. It's also worth reading over the comments as some people have noted that the suggestions in the article aren't always practical: e.g. query optimiser sometimes has trouble with date functions, and the difficulty storing dates as unix timestamps.
Upvotes: 0
Reputation: 449843
mySQL has a native field type for dates: DATE
.
Please don't just say what the best method is, also explain why it's the best method so that I can be convinced to change the entire way birthdates are handled on my site, which is a huge undertaking should that be needed.
Not sure what further explanation to give :) If you want to store dates in a mySQL table, DATE
simply is the one correct choice. It comes with optimized indexing for queries like >
, <
and BETWEEN
, sorts fast, and can deal with any date from the years 1001-9999, so this really is the way to go.
Upvotes: 8
Reputation: 522636
Store it as a DATE
type, since it's very efficient for both storage and operations involving the field (filtering by date, etc.).
Upvotes: 2