How can I store a date in a MySQL database using PHP?

Question

I want to store a date value in a MySQL database.

I have one PHP page called A.php that has 3 inputs: d, m, y, where d = (1 ... 31), m =(1 ... 12) and y = (1970 to 2009).

Now in b.php I collect this values and I need to store in my db.

My DB has a field called dob which is of date type.

How can I construct a variable of type date from d, m, y and store it in the DB?

Answer 1

mysql date fields take the format 'yyyy-mm-dd' so store it as such, but pass it in as a string from PHP, including the dashes.

Answer 2

You can just pass it a string:

$sql = 'insert into tbl (dob) values 
        (\'' . $month . '/' . $day . '/' . $year . '\')';

MySQL will do the correct conversion for you, so you don't even have to worry about it.

Make sure that you've done mysql_real_escape_string to prevent SQL injection on all of your variables.

Answer 3

To construct a variable of type date, use the mktime() function:

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

To store your $date in your database, convert the date to a string:

$sql = "INSERT INTO table (dob) VALUES ('" . date('Y-m-d', $date) . "')";

Alternatively, using mySQLi:

$query = $db->prepare('INSERT INTO table (dob) VALUES (FROM_UNIXTIME(?))');
$query->bind_param('i', $date);
$query->Execute();

Answer 4

It's best to just store it as a UNIX timestamp in the db since it's crossplatform using

mktime()

Then, when you retrieve it from the db, use

date()

to convert it to whatever format you want (e.g. October 31, 2009; 10/31/2009; etc)