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)

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

userinfo.php for html to display(list)

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

Open in new window

Who is Participating?
wolfgang_93Connect With a Mentor Commented:
Yes, you would need to do the same thing for each date field that you are using
on the form.

To convert date format in an insert or update, you are generally converting
from string to a different date format, so you can use the MySQL str_to_date

SQL example:
insert into kkk(xx) values(str_to_date('10-23-2010','%m-%d-%Y'));

PHP example is something like this I think:
$cmd="insert into ... values(... str_to_date('".$reg_user_info['birth_date']."','%m-%d-%Y')) ...";
You can use date_format() to format the date.

PHP - date_format()
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 ...
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Dave BaldwinFixer of ProblemsCommented:
'yyyy-mm-dd' is the standard format for dates in part because it is a 'sortable' format.  This page gives a lot of info about the PHP 'date' function.  It can be used with the 'mktime' function to re-arrange dates.
bianchef9Author Commented:

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?
bianchef9Author Commented:
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 */
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.