want to change display format of date and time field in mysql php

Question

my select statement in php is

  "select * from table";

i use following php statement to display date & time of mysql field

       <?php
       echo $row['mdate']; 
       ?>

the result come like this

   2010-03-09 16:59:18

i want to view the result i following format

    09-03-2010 16:59:18

and

i want to view the result i following format

     09-03-2010 4:59:18 PM

without defining any extra function. i can only modify my echo statement.

     or

i can also modify my select statement

Thanks

Answer 1

See date_format():

select *, date_format(mdate, '%d-%m-%Y %H:%i:%s') AS formated_date from tabl;

And use formated_date in jouw php-code.

Answer 2

You can do the formatting directly in the database as Frank Heikens shows in his answer, or you can do it in PHP. Convert the mySQL date value to a UNIX timestamp using

$timestamp = strtotime($row["mdate"]);

then you can use all options of date() to format it, for example:

echo date("Y-m-d H:i:s", $timestamp); // returns 09-03-2010 16:59:18

both approaches are equally valid; I personally like to modify the value in PHP.