php mysql insert date of birth

Question

I would like some help here. I'm having a registration form with fields like name, surname, dob (date of birth), email etc. When I'm trying to insert into my database everything works fine but for the dob.

The dob I get it from 3 select-option menus like $_POST['year'], $_POST['month'], $_POST['day'], but i haven't find out yet how to put those 3 values in one (dob) in order to insert it in my database correct and not taking the value 0000-00-00.

Any help will be appreciated.

Answer 1

The dob I get it from 3 select-option menus like $_POST['year'], $_POST['month'], $_POST['day']

Just validate and concatenate:

$yr=(integer)$_POST['year'];
$mo=(integer)$_POST['month'];
$da=(integer)$_POST['day'];
if (($yr>1910 && $yr<(integer)date('Y')) 
    && ($mo>0 && $mo<13)
    && ($da>0 && $da<32)) {
      $mysqldate=sprintf("%04d-%02d-%02d", $yr, $mo, $da);
} else {
    trigger_error('date is not valid');
}

Answer 2

Building on rootatwc's reply, one crucial factor when inserting into the database is that DATETIME values must be quoted just like strings.

$name = "Sepp";
$surname = "Blatter";
$dob=$_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['day'];

"insert into birthdays (name, surname, dob) values ('$name', '$surname', '$dob')"

You will also find mysql is quite forgiving, for example June can be expressed as -06- or -6-

As for extracting all entries for a certain year, that is laughably easy - no need to put them into separate fields in your database:

select name from birthdays where YEAR(dob) = '1950';

EDIT

re-reading the question, it is not obvious that the OP is actually using a DATE field, hey, OP, use a DATE field - then you can browse your database and understand what dates are without having to try and decipher a 10 digit number... and you can use all the wonderful date functionality that Mysql comes with ...

Answer 3

How do you want them stored in your database? Do you want them as a UNIX Timestamp, or as some other format?

Without you knowing how you want them formatted, we can't help.

Either way, you should use mktime() to convert to UNIX Timestamp, and then date() when you know how you want it formatted.

Answer 4

If your Apache is on a Linux server, you can use timestamp. Why only on a Linux server? Because Linux understands negative timestamps and that you will probably need them if you have people born before 1.1.1970.

$timestamp = mktime(0,0,0,$month,$day,$year);

Save the timestamp as an int.

echo date('d.m.Y',$timestamp);

to echo it.

Answer 5

$timestamp=mktime(0,0,0,$_POST['month'],$_POST['day'],$_POST['year']);
$dob=date('Y-m-d',$timestamp);

The $dob value is ready to be inserted to your database

Or alternatively you could do:

$dob=$_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['day'];

but you should escape the values first before inserting them to db, or use PDO

Answer 6

change DOB field to <input type="date" name="dob" class="datetime" /> html 5 would generate a datetimepicker for you. And then you can get it $_POST['dob']
And the dob field in database should be TIMESTAMP, DATETIME or DATE format, for universal date format.