CODEWITHSUNDEEP
phpmysqldatedatetime
user2284433
user2284433

Reputation:

Formatting year in PHP from SQL database using date()?

I have upload dates for images stored in a MySQL database as type 'datetime'.

e.g. 2013-04-16 00:20:00

But no matter what I do, I can't seem to get the date to echo correctly in my PHP code. This is what I currently have:

$session_query = mysql_query($sql);
$upload_date_formatted = date('M jS, Y', $session_query['upload_date']);
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

Using the example above, this always gives: 'Uploaded on Jan 1st, 1970.'.

Any ideas? I've tried loads of things, but I just can't seem to get this date to display correctly.

Upvotes: 0

Views: 184

Answers (3)

DaveRandom
DaveRandom

Reputation: 88647

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

You need to call mysql_fetch_assoc() on $session_query to convert it to a usable array.

You will also need to pass $session_query['upload_date'] through strtotime() to convert it to the integer that date() is expecting.

$session_query = mysql_query($sql);
$row = mysql_fetch_assoc($session_query);
$upload_date_formatted = date('M jS, Y', strtotime($row['upload_date']));
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

As it is you are attempting to pass an array key from a non-array, which will be null, which when cast to an integer by date() will be zero, resulting in the 01/01/1970 date you are seeing. This will also raise a couple of errors - you should turn on error reporting as it will help you track down errors in the development environment much more quickly:

error_reporting(-1);
ini_set('display_errors', 1);

Alternatively you can use the DateTime object, and the createFromFormat() method:

$session_query = mysql_query($sql);
$row = mysql_fetch_assoc($session_query);
$date = DateTime::createFromFormat('Y-m-d H:i:s', $row['upload_date']);
$upload_date_formatted = $date->format('M jS, Y');

Upvotes: 4

hd1
hd1

Reputation: 34657

Use the MySQL Date_Format function instead of PHP with the format as '%b %e, %Y'

Upvotes: 0

doublesharp
doublesharp

Reputation: 27609

You need to convert it to a Unix timestamp first using strtotime(), then you can format it using date().

$session_query = mysql_query($sql);
$upload_date_formatted = date( 'M jS, Y', strtotime( $session_query['upload_date'] ) );
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

As a side note, the mysql_* extensions have been deprecated. Try PDO instead.

Upvotes: 0

Related Questions