Help with PHP MySQL date_format?

Question

I have a DATE field in my Table in MySQL called "DateUpdated". Of course it's in yyyy-mm-dd format right? I want it to appear in dd-mm-yyyy format in the web page (or better yet %e-%c-%Y).

I searched online but still confused as how to apply it in my php code. Most people in support forums uses:
SELECT DATE_FORMAT(date,format)

That's where I'm stumped. So where and how do I put that in my code below?

include('connect.php');
$query = "SELECT * FROM CharacterStats ORDER BY ID ASC";

//Run query
$result = mysql_query($query);
if(!$result)
{
die("Could not query the database:
". mysql_error());
}
?>

Some info--
My table: CharacterStats
The date field: DateUpdated

Answer 1

You problem can be handles at two levels:



1) Code Level (PHP)

You can use the PHP date and strtotime functions to re-format the date. Here is an example:




function formatdate( $s )

{

return date( "d-m-Y", strtotime( $s ) );

}



// how to use this function



echo formatdate( $result[ "DateUpdated" ] );

?>



strtotime converts arbitrary strings to date and time value that can be used with other date related functions. date function formats a given date and time value back to a string.



2) Database Level (MySQL) -- not recommended

This query will help:



$query = "SELECT *, DATE_FORMAT( DateUpdated, '%d-%m-%Y' ) AS FormattedDate FROM CharacterStats ORDER BY ID ASC";



Edit ------------

The above query returns one additional column called FormattedDate that contains the DateUpdated in the desired format.

Answer 2

The best way to approach this is to concentrate on your SQL query - so as to avoid sensory overload. =)



First, if you know the columns you want the values from in your CharacterStats table, it is preferred to explicitly "select" them to avoid a tablescan and improve performance; if you don't need everything you will pull back with "select *", just pull what you need, for example, select colA, colB, colC from mytable. Next, your query is missing a "where" clause...not only will your query pull the entire contents of the table, you are likely going to cause a deadlock - meaning that no one else will be able to access the table until your thread either goes to sleep or is disconnected.



But on to the meat: select date(dateupdated),id from characterstats where date(dateupdated)>='2008-01-01' order by id,date(dateupdated) - in other words, use the aggregate date() function to take care of the presentation. Still stumped? The SQL tutorial at www.w3schools.com will be worth your visit as well. Good luck...

Answer 3

DO NOT complicate your sql query by adding formats and so-on: it is inefficient!

$sql = "select `DateUpdated` from `CharacterStats` where (something: 1 for all) order by `id` asc";

Say the date returned by your query is $datum (don't use "date" keyword!), and its value is "2008-11-01"

$mydate = date("d-m-Y", strtotime($datum));

echo ($mydate) -> 01-11-2008.

The "d-m-Y" is the format of the date you want.

Go to www.php.net, search for "date" in the functions. You will have all the parameters for the string format.

"Y-m-d H:i:s", for example, returns "2008-10-01 22:31:45"

There are parmeters to get the month or day in words etc...

Too many options to show here!