asked on

how display and edit date format using PHP and MySQL

I need to display(list) and edit(update) the birth date on a web page using PHP connected to a MySQL database.

All is working ok, that is , I connect to the database from a web page but it displays the date as yyyy-mm-dd instead of dd-mm-yyyy (see userinfo.php in the code box).

Also, I can update the date but only if I type it as yyyy-mm-dd into the web form (see useredit.php in the code box).

What do I need to do?

useredit.php for html to edit(update)

<tr>
<td>Birth Date</td>
<td><input type="text" name="birth_date" maxlength="50" value="
<?
if($form->value("birth_date") == ""){
   echo $session->userinfo['birth_date'];
}else{
   echo $form->value("birth_date");
}
?>">
</td>
<td><? echo $form->error("birth_date"); ?></td>
</tr>

userinfo.php for html to display(list)

*/birth_date */
echo "<b>Birth Date</b> ".$reg_user_info['birth_date']."<br>";

Open in new window

PorterGraphics

You can use date_format() to format the date.

PHP - date_format()

wolfgang_93

I presume that somewhere in your code you have a SQL command passed
to MySQL to fetch the values into the form, for example:

select ...,birth_date,... from ....;

It would fetch any date field in the default MySQL date format of yyyy-mm-dd.

To change the default date display, you can apply the date-format SQL function
to the birth_date field. For example:

select ...,date-format(xx,'%m-%d-%Y'),... from ...

Dave Baldwin

'yyyy-mm-dd' is the standard format for dates in part because it is a 'sortable' format. This page http://us3.php.net/manual/en/function.date.php gives a lot of info about the PHP 'date' function. It can be used with the 'mktime' function http://us3.php.net/manual/en/function.mktime.php to re-arrange dates.

bianchef9

ASKER

wolfgang_93

I have SQL queries for several date fields as
SELECT ..., b_date, e_date, r_date, x_date, ... from

so do I need to use date-format for each date fields ?
is the syntax as date-format(b_date,'%m-$d-%Y') ?

also, how do use date-format for same several fields with INSERT and UPDATE queries?

ASKER CERTIFIED SOLUTION

wolfgang_93

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bianchef9

ASKER

I used your suggestion for the following code which worked to display the human date from the server date.

/* Convert MySQL yyyy-mm-dd to PHP dd-mm-yyyy table contents */
$expiration_date=date("m/d/Y",strtotime($expiration_date));