How to create MySQL field for date format input?

Question

I have a select date field in my HTML form created using Javascript as follow:

<tr>
<td valign="top"><label for="received">Received Date:</label></td>
<td valign="top"><select id="daydropdown" name="rcvdy"></select>
<select id="monthdropdown" name="rcvmon"></select>
<select id="yeardropdown" name="rcvyr"></select> *</td>
</tr>

I would like to register the date input from the form into MySQL database. Do I need to create multiple field columns for each "day" "month" "year" input on my PHP file? Like this

$query=mysql_query('CREATE TABLE shiplog ( shpdy timestamp(2) NOT NULL, shmon timestamp(2) NOT NULL, shpyr timestamp(4) NOT NULL)')

HTML has nothing to do with your database structure! The PHP script in-between takes the submitted data and formats it for the database. Use one DATETIME or TIMESTAMP field! — deceze
– deceze ♦, Commented May 29, 2013 at 18:57

curious_coder · Accepted Answer · 2013-05-31 05:34:33Z

3

In html there's no need to create 3 separate select fields for day, month and year. Instead use an alternative like jquery UI DatePicker or something else. Then in table create a separate column Date(DATE type) and insert.

Jquery datepicker ui : http://jqueryui.com/datepicker/

P.S mysql DATE type is inserted in the following format YYYY-MM-DD.

edited May 31, 2013 at 5:34

answered May 29, 2013 at 19:00

curious_coder

2,4784 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rhinosaurus Over a year ago

I don't know why you would use a varchar for a date field, but I approve the rest of this message.

hangee Over a year ago

@John Blake Thank you so much for the info! It is a lot easier that way...Thanks again!

curious_coder Over a year ago

@Rhinosaurus: It all depends.varchar should be last lazy resort. If the date is only to display and no manipulations are done, i think its ok to put it in varchar.

hangee Over a year ago

@John Blake I used the JQuery datepicker() for the date field input. The database date column field is "input_date DATE NOT NULL". However when I tested input a data using the form and echoed the date $row['input_date'] the result: 000-00-00.

hangee Over a year ago

I solved it using this list($year,$month,$day)=explode('/',$input_date); $timestamp=mktime(0,0,0,$year,$month,$day); $final_date=date('Y-m-d',$timestamp);

|

Collectives™ on Stack Overflow

How to create MySQL field for date format input?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related