[RESOLVED] PHP - MySQL Date Format

[microsoft_boy]

Hi,

I am using a javascript datepicker and below is the html form for the date fields

<input id="dateid1" type="text" size="25"><a href="javascript:NewCal('dateid1','MMDDYYYY')"><img src="datetimepick/cal.gif" width="16" height="16" border="0" alt="Select a date"></a>

I am using another php where I am passing this value

$date = $_POST['dateid1'];

Now I am not able to insert the selected to date into MySQL db (I am using date as the data type).

Here are methods that I've tried so far-

/####Inserts current date only
$date = $_POST['dateid1'];
$dateTime = new DateTime($date);
$formatted_date=date_format ( $dateTime, 'Y-m-d' );
echo $formatted_date;
/

/####Inserts 0000-00-00
$input = date('m-d-Y', $POST['dateid1']);
$d = explode('-', $POST['dateid1']);
$date = mktime(0,0,0,$d[0],$d[1],$d[2]);
echo $date;
/

/#### Inserts 1969-12-31
$input = $_POST["dateid1"];
$newdate = date("Y-m-d", strtotime($input));
echo $newdate;
/

Any help would be really appreciated.

[NogDog]

What does the value from the form actually look like? E.g.:

echo $_POST['dateid1'];

[Weedpacket]

Because if it looks like 02222010 you'll need to change the format to something less obscure.

[microsoft_boy]

Hi,

Actually I got nothing..I checked the database

and the related field (date column) is empty.

any idea pls?

[Markieham]

You're saying your DB field is a 'date' file type?

I don't know how that one works or what it accepts, but I would start by using the UNIX time stamp: time() and perhaps parsing the result from your JS date picker with mktime(..). You can get a readible time/date back again with date(..). For this to work, save your timestamp in an INT db type and work the magic.

I'm quite new to PHP also but I find the UNIX time stamp is usually quite easy to work with.

[microsoft_boy]

All-
Ok, I have changed the db column from date to date1.

Here's the latest results-

echo $_POST['dateid1']; // SHOWS 2-22-2010

$input = $_POST["dateid1"];
$date = date("Y-m-d", strtotime($input));

echo $date; // SHOWS 1969-12-31

any help wud be realy appreciated.

[bradgrafelman]

Markieham;10943702 wrote:

You're saying your DB field is a 'date' file type?

I don't know how that one works or what it accepts,

That's why there's a manual: 10.3.1. The DATETIME, DATE, and TIMESTAMP Types

Markieham;10943702 wrote:

I'm quite new to PHP also but I find the UNIX time stamp is usually quite easy to work with.

Oh really? Quickly, tell me if this date is after or before January 18th, 2004: 1163884029. Can't do it? Try this date instead: 2006-11-18. Much easier, right? That's probably one of the main reasons MySQL stores (and works with) dates in the YYYY-MM-DD standard format.

@: One problem I noticed in your first post is that the date element you're submitting doesn't have a name - only a id. Without the 'name' attribute, it won't get submitted.

Also, that second parameter of the JavaScript function - 'MMDDYYYY' - does that happen to specify the format in which the JS code returns the date selected? If so, wouldn't it make sense to alter that to be the same format that MySQL expects?

[Markieham]

bradgrafelman;10943712 wrote:

That's why there's a manual: 10.3.1. The DATETIME, DATE, and TIMESTAMP Types

Oh really? Quickly, tell me if this date is after or before January 18th, 2004: 1163884029. Can't do it? Try this date instead: 2006-11-18. Much easier, right? That's probably one of the main reasons MySQL stores (and works with) dates in the YYYY-MM-DD standard format.

I'm telling my experience. I never worked with the MySQL TIMESTAMP stuff. So I don't know how that works. (I did just read that manual you linked to)

From my perspective, in scripting I prefer UNIX times. No doubt human readable forms are easier to read for humans ~_~ Funny thing is, from what I read in that manual you posted and some relevant PHP.net articles/comments, to be able of using the timestamp (eg find how long something took, taking two values) you're converting it back to Unix timestamp.

This sounds like a clash of opinions and a moderator frowning down upon new, less experienced members of a community when they try to pitch in. :xbones:

[bradgrafelman]

Markieham;10943716 wrote:

Funny thing is, from what I read in that manual you posted and some relevant PHP.net articles/comments, to be able of using the timestamp (eg find how long something took, taking two values) you're converting it back to Unix timestamp.

Well, you don't have to. Sure, if you're talking about measuring time within a PHP script (e.g. start and stop time to get script execution time), it's easier to use functions such as [man]microtime/man.

If you're working with a DBMS such as MySQL, however, it's much easier to use the DATE/DATETIME column types. Note that no conversion is needed in this case; MySQL has a group of Date and Time Functions that can add/subtract dates, convert dates, etc. etc.

Markieham;10943716 wrote:

This sounds like a clash of opinions and a moderator frowning down upon new, less experienced members of a community when they try to pitch in. :xbones:

My apologies if I came off that way; I do believe Unix timestamps can be useful in certain situations. For the most part, however - especially when dealing with user input - I don't find them quite so useful.

[microsoft_boy]

echo $_POST['dateid1']; // SHOWS 2-22-2010

$input = $_POST["dateid1"];
$date = date("Y-m-d", strtotime($input));

echo $date; // SHOWS 1969-12-31

There are no other ways to convert this into MySQL format huh?!!

Basically I am getting the following from my html form

echo $_POST['dateid1']; ===> SHOWS 2-22-2010

and I would like to insert 2010-2-22 into MySQL db

I've already tried this but no luck
$input = $_POST["dateid1"];
$date = date("Y-m-d", strtotime($input));
echo $date; =======================> SHOWS 1969-12-31

any idea pls?

[bradgrafelman]

If you insist on leaving it in the M-DD-YYYY format, then you should be able to do some string manipulation to get the pieces of the date. Here's an example:

// assume $date is "2-22-2010"
$date = explode('-', $date);
$date = "$date[2]-" . sprintf('%02u', $date[0]) . "-$date[1]";

// $date is now "2010-02-22"

[NogDog]

If you are stuck with the form submitting it as m-d-y, then you'll need to break it into its components, then recombine them.

$dbDate = false;
$parts = explode('-', $_POST['dateid1']);
if(count($parts) == 3) // got all 3 parts
{
   if(checkdate($parts[0], $parts[1], $parts[2])) // valid date
   {
      $dbDate = sprintf("%04d-%02d-%02d", $parts[2], $parts[0], $parts[1]);
   }
}
if($dbDate === false)
{
   // handle invalid date error
}

[microsoft_boy]

TO : bradgrafelman

You're a genius. I am able to store the date (coming from Javascript datepicker) now.

Code recommended by ' bradgrafelman'

// assume $date is "2-22-2010"
$date = explode('-', $date);
$date = "$date[2]-" . sprintf('%02u', $date[0]) . "-$date[1]";
// $date is now "2010-02-22"

I haven't yet tried the code provided by 'NogDog' but it looks like the code is similar to the above mentioned snippet.

Thanks much again all.

[bradgrafelman]

NogDog's code is more versatile in that it provides a bit of error checking using the [man]checkdate/man function, something I forgot to mention in my post.

[microsoft_boy]

Oh OK then I think I will go with the following -- Thanks much for your help, really appreciate it..

// assume $date is "2-22-2010"
$date = explode('-', $date);
$date = "$date[2]-" . sprintf('%02u', $date[0]) . "-$date[1]";
// $date is now "2010-02-22"