Converting MYSQL DATEs for PHP

Posted: July 30th, 2012 | Author: | Filed under: Internet, MYSQL, PHP, Programming | No Comments »

A site I am working on stores the date of new entries to the database by using MYSQL’s CURDATE() function. So if we’re adding a new entry with the make, model and date to the database, the SELECT query looks like this:

"INSERT INTO table_name (make, model, date) VALUES ($make, $model, CURDATE())"

This works fine, but when I then query the database, like so:

"SELECT make, model, date FROM table_name WHERE ..."

The date returned is in MYSQL’s date format, which is YYYY-MM-DD.
I was looking around for a quick and easy way to convert this in PHP to the UK DD-MM-YYYY format, and kept coming up with longwinded ideas for converting the MYSQL date to a UNIX timestamp, and then using all kinds of guff to format this as required.
It turns out the answer’s much simpler than that, if you use MYSQL’s DATE_FORMAT function. By passing in your date column and the format in which you want it presented you can create a new variable to query. So the previous query becomes:

"SELECT make, model DATE_FORMAT(date, '%d/%m/%Y') as formatted_date FROM table_name WHERE ..."

The %d/%m/%Y part is where you define how the date should be formatted. Dan Winchester has an excellent and comprehensive list of options on his website.

Now, instead of your PHP looking like:

$make = $row['make'];
$model = $row['model'];
$date = $row['date'];

it becomes:

$make = $row['make'];
$model = $row['model'];
$date = $row['formatted_date'];

thus returning your date information nicely pre-formatted by MYSQL.

Leave a Reply