Page 1 of 1

DATE issues

Posted: Fri Aug 27, 2004 10:18 am
by cdickson
I actually have 2 date issues:

1. I'm trying to use the date() function to format a date that is stored in a MySQL database, but no matter what date is stored in the database, it keeps returning 12-31-1969. The correct date to be returned is 08-27-2004. The code that I'm using is:

Code: Select all

<?php echo date('m-d-Y',$row3['order_date']); ?>

2. The MySQL date format is yyyy-mm-dd, but in the US most people are used to the dd-mm-yyyy format. Can I do something with the input field to accommodate this format?

Posted: Fri Aug 27, 2004 10:34 am
by feyd
http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html

DATE_FORMAT is the function you probably want for #1.

As for 2, you can use a regular expression to capture the individual numbers and transform them into a date string for timestamp. However, I believe most of us don't accept dates directly like this, because there are hundreds of formats. I opt for having drop downs for day and month, and a textbox for year when I want a date from the user.

Posted: Fri Aug 27, 2004 11:01 am
by cdickson
Thanks, feyd - I'll use DATE_FORMAT as suggested for #1.

For #2: When you use the drop downs and textbox for the dates, how do you correctly format the input to add it to the database? Do you have a separate field for each component of the date?

What I didn't mention before is that I am currently using NOW() to get the date.

Posted: Fri Aug 27, 2004 11:09 am
by feyd
the processing script validates the date and combines them into an accepted date for mysql.

Posted: Fri Aug 27, 2004 11:46 am
by cdickson
So my current script reads:

Code: Select all

$query = "INSERT INTO table (order_date) VALUES (NOW())";
Although I've checked the PHP manual, I can't find information on how to properly re-format the above in order to get the database to accept the information in the correct format.

Obviously I need to change the coding to get the database to accept the three variables - month, day, year - as one date, which is the field "order_date" in my database. I should change the (NOW()) to what?

Posted: Fri Aug 27, 2004 12:10 pm
by feyd
to the validated date your create in the processing script.

Posted: Fri Aug 27, 2004 1:02 pm
by cdickson
I've never done this before, and I don't expect you to do it for me, but I haven't been able to locate what I'm looking for here or in php.net. If you could just point me in the right direction...

Is this the right idea?

Code: Select all

//Validate month
if (is_numeric ($month)) {
  $order_date = $month . '-';
} else {
  echo '<p>Please select the current month.</p>';

//Validate day
if (is_numeric ($day)) {
  $order_date .= $day . '-';
} else {
  echo '<p>Please select today''s date.</p>';

//Validate year
if (is_numeric ($year) AND strlen ($year) == 4) {
  //Make sure year is correct
  if ($year != 2004) {
     echo '<p> You must select the current year.</p>';
  } else {
     $order_date .= $year;
  }//End 2nd conditional

Posted: Fri Aug 27, 2004 1:05 pm
by feyd
that won't be accepted by mysql.. you need to insert it in their format. The basics are current though.

Posted: Fri Aug 27, 2004 2:05 pm
by cdickson
Now that I've made the changes to the database structure to start working on the dates, something has gone awry with my order_no field, which is the primary key.

It is an auto-incremented field, but now it wants to keep assigning the number 127 to every order that is submitted, and I get the error message
Duplicate entry '127' for key 1.
The only thing I changed in the database was deleted the "order_date" field and added "month", "day" and "year".

Do you know why this happened?

Posted: Fri Aug 27, 2004 2:10 pm
by feyd
you didn't need to alter the table structure.. :?

Posted: Fri Aug 27, 2004 3:55 pm
by cdickson
Evidence that 50+ posts does not necessarily make one a Developer. :oops: