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?
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
$today = date("Y-m-d");
That is the correct format for inserting into MySQL.
If the publicationDate column is of type DATE (which it should really be) you need to format the date in one of the formats MySql recognizes, not a human-readable string.
Of those, most common are date("Ymd") or date("Y-m-d") if you like hyphens.
Use this instead
$today = date("Y-m-d");
MySQL date only stores the date not the time like YYYY-MM-DD
If you want to store the time as well you can use a DATETIME field which is this: YYYY-MM-DD HH:MM:SS or you could store the timestamp as a string.
The value should be a string, formatted to MySQL's format: Y-m-d.
