Reformatting MySQL date (yyyy-mm-dd) from a database with PHP
The following article assumes you have a solid understanding of PHP 4, PHP 5 and MySQL, and skips all basic setup instructions and explanations in effort to provide you with quick, concise information. It also assumes you have a MySQL database setup with a a field called date that is set as type: DATE.
Storing the current date along with new data into a MySQL database table is a piece of cake. Unfortunately there is little-to-no documentation about how to format a stored date using PHP at runtime.
Assuming your database is already set up and you have a table with a field named date of type DATE, your query would look something like this:
-
<?php
-
//This assumes you have already connected to your database and that you have created a table called ‘my_table_name’ with fields ‘content’ and ‘date’
-
-
$my_content = "This is a sweet-ass tutorial.";
-
-
$query = "INSERT INTO my_table_name (
-
content, date
-
) VALUE (
-
$my_content, CURDATE()
-
)";
-
?>
The native PHP function CURDATE() saves the current month, day and year into your database table’s ‘date’ field in the format yyyy-mm-dd. So now we’ve got our date stored, which is awesome, but chances are we’ll eventually need to retrieve and display that date next to a heading, article or other saved content at runtime, and it’s not in the most widely-used format.
No need for crazy string replacement functions, insane super-nerd loops or anything else that makes your brain hurt. Behold:
-
<?php
-
// reformatting MySQL date from the stored format (yyyy-mm-dd) to (mm-dd-yyyy)
-
// set a temp var to store and convert our date/time from the database to a more human, legible format for display
-
// you will need to have already retrieved the corresponding date from the database in some other query
-
-
-
// display the date
-
?>
This echoes Date of entry: Month/Date/Year, which is a much more common and legible way of writing the date. You can replace the slashes with periods, dashes or switch the order of m d Y to get the desired result, and of course assemble and customize your final echoed string as needed.
