How do I make nice formated date with MySQL & PHP

Question

SQL

/* The SQL syntax I used to create my 'time' field:
    ALTER TABLE  `files` ADD  `time` DATE NOT NULL AFTER  `id`;
*/

then I added the function 'NOW()' to the values I wanted.

PHP

<?php
// Selects all the files from my table 'files'. It contains 'title', 'content', 'time'.
$get_files = mysql_query("SELECT * FROM
    files ORDER BY id 
    DESC LIMIT 0, 3", $connection);

echo "<div class='display-files'>"; // Wraps this around the Article.

while($files = mysql_fetch_array($get_files)) {
    $title_get = $files['title']; // Gets the 'title' from table 'files'.
    $content_get = $files['content']; // Gets the 'content' from table 'files'.
    $date_get = $files['date']; // Gets the 'date' from table 'files'.

    echo "<article>"; // Article is just to organize the content.
    echo "<h2>{$title_get}</h2>"; // Displays 'title'.
    echo "<p style='white-space: pre-line'>{$content_get}</p>"; // Displays 'content'.   
    echo "<hr /><p>Created: <em>{$date_get}</em><br /></p>"; // Displays 'date'.
    echo "</article>";
}
echo "</div>";
?>

The variables should have explained well enough, but I added comments . . . just in case for the people who have troubles reading my code. Plus, first question I am doing.

The Result: 2016-03-14

Anyways, I am here to ask if there is a way to get the 'time' from my DB/Database and give it a nice readable format.

e.g: Mar 14 2016

**WARNING**: If you're just learning PHP, please, do not learn the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It's awful and has been removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Make **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will end up with severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Mar 14 '16 at 17:40
http://php.net/date http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format — Marc B, Mar 14 '16 at 17:41

score 2 · Accepted Answer · answered Mar 14 '16 at 17:42

2

Try something like this:

  $date_get = $files['date']; // "2016-03-14"
  echo date('M d Y',strtotime($date_get)); // Mar 14 2016

You may wanna take a look at this link: PHP Date Time functions

Hope this helps.

Peace!

answered Mar 14 '16 at 17:42

Indrasis Datta

8,692
2
14
32

Also [`strftime`](http://php.net/manual/en/function.strftime.php). – tadman Mar 14 '16 at 17:45
or [DATE_FORMAT](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) – TarranJones Mar 14 '16 at 17:54
@TarranJones That is the best option anyway. The simplest, fastest and the most reliable. There's a guarantee that MySQL can manage it's own date values. While PHP could have problems. (I never had, but could happen). You should move that comment to an answer. – Ismael Miguel Mar 14 '16 at 18:02
Hey thanks, I got it now. I was new to the dates so, had a bit of a trouble figuring out the syntax making it into a nice format, ANYWAYS, thanks for the help! – Mar 14 '16 at 18:45

score 0 · Answer 2 · answered Mar 15 '16 at 03:54

You can add DATE_FORMAT() in the select list:

SELECT ..., DATE_FORMAT(`date`, '%b %d %Y') AS `date`
FROM files ORDER BY id 
DESC LIMIT 0, 3

Below is a demo of the format effect:

mysql> SELECT DATE_FORMAT('2016-03-14', '%b %d %Y');
+---------------------------------------+
| DATE_FORMAT('2016-03-14', '%b %d %Y') |
+---------------------------------------+
| Mar 14 2016                           |
+---------------------------------------+
1 row in set (0.00 sec)

How do I make nice formated date with MySQL & PHP

SQL

PHP

2 Answers2