How to format the date in PHP and SQL?

Question

I have a field in one of my tables called "publicationDate" and its data type is date. However, when I make a query that should show today's date, I am having a problem on how to format it. It always shows 0000-00-00.

This is my variable:

$today = date("F j, Y, g:i a");

This is my query:

$query = "INSERT INTO articles (summary, publicationDate) VALUES ('{$content}', '{$today}')";

What am I doing wrong?

Answer 1

Try this:

$today = date("Y-m-d"); 
// 2011-09-24

Your string returns:

$today = date("F j, Y, g:i a");
// September 24, 2011, 6:39 am

---

Edit:

To save date and time you can use datetime or timestamp:

$today = time(); // timestamp

Change your database field to timestamp.

or

$today = date("Y-m-d H:i:s"); // datetime

Change your database field to datetime.

To select your data from database you can to this:

$result = mysql_query("SELECT date FROM table");
$row = mysql_fetch_array($result);

// field is timestamp
echo date("F j, Y, g:i a", $row['date']);
// or
// field is datetime
echo date("F j, Y, g:i a", strtotime($row['date']));

More informations here: http://at.php.net/manual/en/function.date.php

Answer 2

$today = date("Y-m-d");

That is the correct format for inserting into MySQL.