INSERT/UPDATE form - how to combine 5 form field to submit to one database field? Datetime

I have drop down menus for day, month, year, hours and minutes to allow users to submit events to an events database table.

In our MySQLi Insert code we currently have the following for the datetime field insert using one of the drop down fields (year):
$InsertQuery->bindColumn("SMTeventdate", "t", "".((isset($_POST["SMTeventyear"]))?$_POST["SMTeventyear"]:"") ."", "WA_DEFAULT");

Is it possible to combine all 5 of our form fields for insertion into this one datetime field (SMTeventdate)? I need to submit in the following format:
yyyy/mm/dd hh:mm:ss

I know I need to combine the fields into one 'array' (is it?), but I'm not sure whether I have to do that before submitting the form (onchange event?) or whether it's possible to have the INSERT code handle all five form fields for submission into the one form field.

Also, how would this code change for an UPDATE form?
Thank you
NJ

in all honesty, if you are going to use 5 elements to build the date like this, i would use 5 fields in the database to store the date rather than trying to concatenate the 5 fields to one. this is just adding a layer of complexity to the insert and update process that is not needed.

with using the 5 fields, you can combine them to create a date using the STR_TO_DATE function in the recordsets where you displaythe date and or time:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

for example:
if your table has 5 fields for each date piece:

+---------+----------+--------+---------+------------+------------+
| theYear | theMonth | theDay | theHour | theMinutes | theSeconds |
+---------+----------+--------+---------+------------+------------+
| 2014    | 2        | 15     | 13      | 45         | 26         |
+---------+----------+--------+---------+------------+------------+
| 2013    | 5        | 12     | 5       | 15         | 45         |
+---------+----------+--------+---------+------------+------------+

you can use STR_TO_DATE and CONCAT in your SQL to create a date where you want to display the information:

SELECT STR_TO_DATE(CONCAT(theYear,'-',theMonth,'-',theDay,' ',theHours,':',theMinutes,':',theSeconds),'%Y-%m-%d %H:%i:%s') FROM table

Hi Jason. Maybe still the fall out from your recent server crash, but I didn't receive notification of your replies on this.

Is there an easier way to submit a date and time to a datetime field? 5 form entries, 5 database fields, str-to-date and concat...there's got to be an easier way? I would appreciate your advice and guidance.

I'm basically trying to create an ADD event and EDIT event form for submission to database in as simple a format as possible.
Thank you.
NJ

I Would use 2 fields, a date picker to choose the date and a second field to choose the time, store the date in one field of the database and the time in a second field of the database.

I'm having problems with WebAssist's Datepicker. When I apply it to a form nothing happens when I click in the date field.
I attach the page for inspection.

Thank you.
NJ

conflictying jquery versions are appiled to the page,

the datepicker adds jquery in the head of the page:
<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>


you are adding it again near the end of the page:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

jquery can only be applied to the page once.

WebAssist Datepicker has inserted the following on lines 75-77:
<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
</script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

I don't understand the erroneous closing "</script>" tag - perhaps that's a bug.
At the bottom of the page, on line 298, for the purposes of using Bootstrap, I have:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

So, which should I remove? I need the bottom referenced jquery for Bootstrap.

With regard the date and time entry in general, I'm trying to remove the possibility for my client to enter incorrect data....

Date: Using the Datepicker, how can I display the date in dd/mm/yyyy format in the form field, once the date is selected from the Datepicker, but have it insert into the database in yyyy/mm/dd format?

Time: How do I ensure that people don't enter 10:30, for example, as opposed to the correct 22:30? Just having a time field as input type text is going to open up an opportunity for client error to occur. I could ask them, via a drop-down menu, to select AM or PM, but I'm not sure how to then convert all that into a single time insert. I'd like to maintain the field as TIME in the database because I need to display the events chronologically.

I really would appreciate your advice on the best way to handle date and time entry as this all seems overly complicated.

Thank you.
NJ

replace the one at the top:
<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

with the newer one:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

and delete the reference at line 298

you can also remove the extra closing script tag.


in the date picker settings, you can select the date format you wish to use, you will then need to add code to reformat the date when it is submitted:

for example, assuming the name of the datepicker element is set to DatePicker:

<?php
if(isset($_POST['DatePicker'])) {
    $_POST['DatePicker'] = date("Y-m-d", strtotime($_POST['DatePicker']));
}
?>

for the time field, I Would use a select list, format the options in 24 hour time, so have a selection for 10:30 and another 22:30, etc.

Thank you Jason. The extra closing script tag is inserted whenever I add a Datepicker to the page so can I log that as a bug with you?

The sample code you've provided, to reformat the date when it is submitted...does that go above the Insert code or is that held within the body section?

I had considered a select list for the time but was concerned about the length of the list.
Also, relating to how the time is submitted to the database, I created a TIME field in my MySQL database but when I look at that in an ODBC connected Access database whilst it does log the correct time from my form, it also appears to store the current date. Will that cause me problems?

Appreciate the help. Thank you.
NJ