Jump to content

Form input with two date formats in two different input fields from select date


dwperry1

Recommended Posts

I have a calendar select date function for my form that returns the date in the calendar format for USA: 02/16/2012.  I need to have this appear as is for the form and in the db for the 'record_date' column, but I need to format this date in mysql DATE format (2012-02-16) and submit it at the same time with another column name 'new_date' in the database in a hidden input field.  Is there a way to do this possibly with a temporary table or something?

 

Any ideas would be welcome.

 

Doug

 

 

 

Link to comment
Share on other sites

Convert the input to a UNIX timestamp and then convert it again into the MySQL DATE format.

$input = '02/16/2012';

$unix_timestamp = strtotime($input);

 

Then use the FROM_UNIXTIME() function in your MySQL query to change it to DATETIME.

INSERT INTO table (record_date) VALUES (FROM_UNIXTIME($unix_timestamp));

Link to comment
Share on other sites

 

scootstah

 

I have tried many variations of this, but since the $input value isn't recognized by the database yet (since we are selecting the value for record_date at the same time), the insert statement works, but when I view the database, the value in the column is "$input", not the date.

 

strtotime(record_date);

$unix_timestamp = strtotime($input);

 

I am choosing the value for record_date (02/16/2012) at the same time I need to convert that value to 2012-02-16 and INSERT it into the db AS new_date.

 

Any ideas?

 

Doug

 

Link to comment
Share on other sites

Pikachu2000,

 

I'm sure I can use your help on this one, biut first to answer your questions:

 

The '$input' was just an answer to the post from scootstah using his example.

 

The reason I am storing the calendar format as VARCHAR MM/DD/YYYY is because it actually needs to show this way on the form when it is called up and it is also the format used with the javaScript DatePicker I am using for my fill in form.

 

I thought it would be easy to reformat this to Date format, which it is:

 

                            $originalDate = "record_date";

$new_Date = date("Y-m-d", strtotime($originalDate));

 

Or any number of combinations.

 

What I would like to do is use the DatePicker allowing the calendar format in one field and use this date to convert to the MySQL format in another field on the form when submitted populating both columns in the db.  I don't know if this is possible since the db doesn't have any information to convert until the form is submitted.  I know there must be a way to convert the date upon submission, but I haven't been able to come up with one yet.

 

I am new to PHP and MySQL, so I am open to your suggestions and ideas.

 

Thanks!

 

Doug

 

Link to comment
Share on other sites

There is never really a need to store a date in that format, especially when it means you end up storing the same information twice. When you store a date in the DB as YYYY-MM-DD, you can format it however it needs to be when you retrieve it, using MySQL's DATE_FORMAT() function. Likewise, when inserting the date, you can use MySQL's STR_TO_DATE() function, or php's date and strtotime functions.

 

Additionally, most datepickers allow you to specify the format the date is displayed in, as well as the format it gets sent in with the form submission.

Link to comment
Share on other sites

Pikachu2000,

 

I have tried the folowing: "INSERT date_format( str_to_date( record_date, '%m-%d-%Y' )) AS new_date FROM daily_dales";

 

However, since record_date doesn't have a value, nothing is converted.  This is not really my question, but lets get to your comment.

 

If I change the DatePicker to output the Mysql format, then this is what will show in the form and this is not what I want.  I need the calendar date to show in the form.  The form is what populates the db.  I then have the same problem in reverse..  How to show the calendar format in the form befopre accessing the db.

 

What do you suggest?

 

 

Link to comment
Share on other sites

I have a datepicker that is formatted to show dates dd/mm/yyyy to add them to mysql database I use a function to 'amend' the format:

function date2mysql($date){
    $day=substr($date,0,2);
    $month=substr($date,3,2);
    $year=substr($date,-4);
    $date=$year."-".$month."-".$day;
    return $date;
}

perhaps you could do something similar....

Link to comment
Share on other sites

Pikachu2000,

 

You can download the DatePicker here: http://www.rainforestnet.com

 

It is free I believe and you can see the code there.

 

Here is my code that applies the date to the form field from the DatePicker:

 

Date: <input type="text" name="record_date" id="record_date" value="" onFocus="javascript:vDateType='1';" onKeyUp="DateFormat(this,this.value,event,false,'1')" onBlur="DateFormat(this,this.value,event,true,'1');"><a href="javascript:NewCal('record_date','mmddyyyy')"><img src="graphics/cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>

 

After getting the date into the form field, I need to get the MySQL equivalent in another form field value (new_date) when the form is submitted.

 

Thanks for your help.

 

 

harristweed,

 

If I do it the way you suggest, the new_date column will be NULL and I will have to update the db in order for the record_date column to have a value so I can convert the format for the new_date column.

 

What I am looking for is to find a way to take the output from the DatePicker that populates the record date field in a calendar format, and reformat this value to Mysql format yyyy-mm-dd and populate new_date field before or upon submission.

 

I tried using a js copy function, but it only works if the date is typed in.

 

Any ideas?

 

Link to comment
Share on other sites

Pikachu2000,

 

Thanks for taking a look.  The problem I am having relates to the way my form works with the database.  I think this can be approached a different way.  Here's how it works:

 

I have three forms: Enter, Update and View.

 

I enter daily sales on the first form (Enter).

 

The input from the form populates the database.

 

Then I wiew the database results on another form by query (View).

 

However, some of the fields don't show any results because they are dependent on values input from the first form (like the record_date which cannot be read because it is    not in the right format).

 

However, when I update the form (Update) I am able to now change the format of the record_date column and populate the new_date column with the MySQL DATE format and use this new_date to sort my results by.

 

Even if I could format the date correctly on the Enter form, this doesn't solve the problem of, let's say, adding my daily_customer_count to the month_to_date_customer_count.

 

What would solve the entire problem is to somehow submit the Enter form and have it automatically Update the db and do the calculations for the blank fields and the date conversion and show the results after one submission.

 

Right now it nall works fine, but I have to Enter the form, Update the results and then View the results in three steps in order for all of the information to appear.

 

I hope this helps explain how it works and what I need.

 

Thanks for not giving up on me!

 

Doug

 

Link to comment
Share on other sites

litebearer,

 

The entire program is quite big and is on my WAMP set-up on my computer.  Because I have such spotty internet access where I am working on this project, it would slow me down too much to work on it online.  The code is too involved to display it on this forum.

 

Sorry,

 

Doug

 

Link to comment
Share on other sites

harristweed,

 

I already bave both.  What I need is a way to submit the form, have the database use the submitted information to add to the existing information already in the database and return a result when I submit the form.

 

Or, instead of having my php form do all of the calculations, have my database do them on entry if possible so when I view the results, all of the calculations are done.

 

Here is an example.

 

Customer count for the month is already totaled by summing that column in the db every day from the beginning of the month to the current date (or date of the query).

I want to add today's customer count to that total when I submit my form and have it show the result of today's entry added to the customer count total that's already in the database.

 

So I need to return the compiled results, not just todays entry when I submit the form.

 

I hope this helps explan what I'm asking for.

 

 

Link to comment
Share on other sites

I’d tackle your requirement like this….

<?php
// date function
function date2mysql($date){
    $day=substr($date,0,2);
    $month=substr($date,3,2);
    $year=substr($date,-4);
    $date=$year."-".$month."-".$day;
    return $date;
}
//has the form been posted?
if($_POST['submit']=="submit"){
    //Get the old customer total
  $customer_count = mysql_result(mysql_query("SELECT COUNT(*) as Num  FROM table_name WHERE condition' "),0) ;
  // format the date and add posted info to database
  $date_to_mysql=date2mysql($_POST['datepicker']);

  //update database with post infomation
  
  $number_customers = $new_customers + $customer_count;
  
  //display totals
  echo "<p>Total customerrs =".$number_customers."</p>\n"; 
}else{
    //display form
        echo "<form id=\"form\"  action=\"$_SERVER[php_SELF]\" method=\"post\" >\n";
        ?>
<input type="text" name="form_stuff">
<input type="submit" name="submit">
</form>

Link to comment
Share on other sites

harristweed,

 

When I use this code I get the following error when I bring up the form:

 

"Notice: Undefined index: datepicker in C:\wamp\www\books\daily_sales_program_enter.php on line 170"

 

Thanks for your input on this problem.  I think you are on the right track, but I need to reformat my script and try a couple of things.  I will report what I find.

 

Thanks again to all for your help.

 

Doug

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  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.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.