CODEWITHSUNDEEP
phpmysqldate
ProgrammerGirl
ProgrammerGirl

Reputation: 3223

What is the best way to store user BIRTHDATES in MySQL/PHP?

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

Answers (4)

Syed Tanhel
Syed Tanhel

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

Ozzah
Ozzah

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

Pekka
Pekka

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

deceze
deceze

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

Related Questions