close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

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

Thread began 9/26/2014 3:12 am by Nathon Jones Web Design | Last modified 10/01/2014 9:22 am by Jason Byrnes | 465 views | 14 replies |

Nathon Jones Web Design

INSERT/UPDATE form - how to combine 5 form fields 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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Jason ByrnesWebAssist

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
Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...