Changing date format from form to database

[Wickham]

This is an input tag from a simple form to show a proposed future delivery date:-

 

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?

Edited November 3, 2009 by Wickham

[falkencreative]

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 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];
}

[Wickham]

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.

 

 

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 ( "

Unable to select table

");

 

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.

[Wickham]

I got there in the end.

I used

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 ( "

Unable to select table

");

 

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).