Jump to content

Changing date format from form to database


Wickham

Recommended Posts

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 by Wickham
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...