• Gender: Male
  • From: Salisbury UK
  • Registered: 2008-12-20
  • Posts: 1,231

Topic: Changing date format from form to database

This is an input tag from a simple form to show a proposed future delivery date:-
<input id="date" type='text' name='date10' value='<?php echo date("d F Y", strtotime('+10 days'));?>' size='30'>

which shows in the form input box as 12 November 2009 (+10 days from current date) and is in the format I want to display.

When submitted to the MySQL database the date is rejected because the database can only accept a date in a format of y-m-d or y/m/d.

How can I change the format inside the input tag so that the date is sent to the database in a different format but still keep the form in the format I want?

I can code the input tag in the correct format for the database but then the display is not in the format I want.

It seems that the value="..." in the form input tag serves two purposes, to display the value in the form and to send the value to the database, so if I format for the display it sends the wrong format to the database; if I try to put two values in the input tag it objects or sends the wrong one to the database. I have thought of having a dummy input tag with display: hidden but can't get it to work yet.

Additionally, if the format is y-m-d (which works but I don't want) the viewer can change the date if he doesn't like it and the revised date will go to the database, and I'd like this to happen with my desired format. I may have to put the date outside the form, but then how do I get it into the database?

Last edited by Wickham (2009-11-03 02:12:44)

  • Gender: Male
  • From: Chico/Sacramento, CA
  • Registered: 2008-12-19
  • Posts: 2,343

Re: Changing date format from form to database

How can I change the format inside the input tag so that the date is sent to the database in a different format but still keep the form in the format I want?

This is something I've had trouble with in the past...  I think the solution here is to create two translation functions that convert to and from the human readable format you want to display within the <input> with the timestamp format (year-month-day) that you want to save it to the database in.

Here's one example of what I've done:

All my dates are stored in the database as year-month-day-time (for example, "2008-12-12 00:00:00"). If the user enters a date in a different format "month/day/year" for example, it gets converted before being saved into the database.

I then have two functions that convert to and from that format (which I've called "timestamp") and a more usable format ("month/day/year").

// convert current time to timestamp
// input: none
// returns now() in timestamp (0000-00-00 00:00:00) format
function getTime()
{
    return date("Y-m-d H:i:s", time());
} 
    
// converts date (00/00/00) to timestamp
// input: date, delimiting character(s)
// output: string in timestamp format
function convertDateToTimestamp($date, $delimiter)
{
    $date = explode($delimiter, $date);
    return '20'.$date[2].'-'.$date[0].'-'.$date[1].' 00:00:00';
}
    
// convert timestamp to date (00/00/00)
// input: timestamp, delimiter
// output: string in date format
function convertTimestampToDate($timestamp, $delimiter)
{
    $date = substr($timestamp, 2, 8);
    $date = explode("-", $date);
    return $date[1] . $delimiter . $date[2] . $delimiter . $date[0];
}
  • Gender: Male
  • From: Salisbury UK
  • Registered: 2008-12-20
  • Posts: 1,231

Re: Changing date format from form to database

Thanks for your reply; I haven't had time to try your codes yet; I took the easy way out temporarily by having one bit of PHP for the form input value to calculate the current date plus 10 days in d F Y format so that it shows as 13 November 2009 in the form and then a variable for the MySQL submission which has the same calculation but in the format y-m-d which MySQL can accept.

<input id="date" type='text' name='date10' value='<?php echo date("d F Y", strtotime('+10 days'));?>' size='30'>

and for the MySQL link (part shown):-

$date =  date("y-m-d", strtotime('+10 days'));

$result=MYSQL_QUERY("INSERT INTO my-table (id, place, date, time)".
"VALUES ('NULL', '$place', '$date', '$time')") or die ( "<p><span style=\"color: red;\">Unable to select table</span></p>");

The form and the database show the same date (in different formats) but it means that if someone edits the date in the form, the value="..." in the form isn't taken to the database, it's the other variable which I haven't been able to link to the form value yet.

I assume that as the form value is echo date() it must be a string, so what I need is a function to convert a string into a different format that I can feed outside the form to a new variable that I can send to MySQL. Every time I change the format inside the form it changes the format of the form display which I don't want.

  • Gender: Male
  • From: Salisbury UK
  • Registered: 2008-12-20
  • Posts: 1,231

Re: Changing date format from form to database

I got there in the end.
I used
<input id="date" type='text' name='date10' value='<?php echo date("d F Y", strtotime('+10 days'));?>' size='30'>
in the form code to show the future date in the full format 14 November 2009 (today + 10 days)

The name="date10" in the form creates a variable $date10
$date10 = $_POST['date10'];

and then I created a new variable taking the string from date10 in the full format and converting it into a y-m-d format
$date = date('y-m-d', strtotime("$date10"));

which gets fed to the database
$result=MYSQL_QUERY("INSERT INTO my-table (id, place, date, time)".
"VALUES ('NULL', '$place', '$date', '$time')") or die ( "<p><span style=\"color: red;\">Unable to select table</span></p>");

so the form's proposed future date can now be edited by a viewer and will go into the database (unless he edits it in an incorrect way, of course, but then I have the timestamp in the database as a check).