1

I have a question about inserting dynamic table with time values. Here is example of the table that I have to insert in db:

Schedule for 12/15/2015
08:30 AM - 09:30 AM     Remove
09:30 AM - 10:30 AM     Remove
10:30 AM - 11:30 AM     Remove
11:30 AM - 12:30 PM     Remove
02:30 PM - 03:30 PM     Remove

I do not have a problem to insert values if my time difference is always the same. Like first few time slots are increased for 30 min but then I have a gap from 12:00pm all the way until 5:00pm. My problem is there. I do not know what is the best way to save these values. Since values are created dynamically and all of them are linked to the same date. If I do not have time breaks that would be really easy but in this case where I have time gap looks really complicated. If anyone can tell me what is the best way to store this in database please let me know. For now I just store start time, end time, date and interval that my time increase each time. That looks like this:

ID  Date        Start               End              Interval
4   2015-12-15  08:30:00.0000000    15:30:00.0000000    60  

In this case If I want to pull out values with the query and display time slots in the table I'm only able to do that with 60min intervals. But I have a gap from 12:30PM until 2:30PM. That gap always can be different depends on user wish.

Here is my query where I build my table:

<cfscript>
    param name = "FORM.datepicker" type = "date";
    param name = "FORM.stime" type = "time";
    param name = "FORM.etime" type = "time";
    param name = "FORM.meeting" type = "range" min = "5" max = "60";

    TimeStart = createDateTime(year(FORM.datepicker), month(FORM.datepicker), day(FORM.datepicker), hour(FORM.stime), minute(FORM.stime), 0);
    TimeEnd = createDateTime(year(FORM.datepicker), month(FORM.datepicker), day(FORM.datepicker), hour(FORM.etime), minute(FORM.etime), 0);
    meetingLength = dateAdd("n", FORM.meeting*-1, TimeEnd); 
</cfscript>

<form name="myForm" id="myForm" method="post" autocomplete="off">
    <cfoutput>
        <table>
            <thead>
                <tr>
                    <th>Schedule for #dateFormat(FORM.datepicker, "mm/dd/yyyy")#</th>
                </tr>
            </thead>
            <tbody>
                <cfloop condition="TimeStart LTE meetingLength">
                    <cfset TimeEnd = dateAdd("n", FORM.meeting, TimeStart)>
                    <tr>
                        <td></td>
                        <td>#timeFormat(TimeStart, "hh:mm tt")# - #timeFormat(TimeEnd, "hh:mm tt")#</td>
                        <td><a class="remove" onClick ="removeTime($(this))">Remove</a></td>
                    </tr>
                    <cfset TimeStart = dateAdd("n", FORM.meeting, TimeStart)>
                </cfloop>
                <tr>
                    <td>
                    <input type="button" name="Submit" value="Save" onClick="saveSchedule('#FORM.datepicker#','#FORM.stime#','#FORM.etime#','#FORM.meeting#')" />
                    </td>
                </tr>
            </tbody>
        </table>
    </cfoutput>

Code for inserting records:

<cffunction name="Save" access="remote" output="no" returnformat='JSON'>
    <cfargument name="datepicker" type="date" required="yes">
    <cfargument name="stime" type="numeric" required="yes">
    <cfargument name="etime" type="numeric" required="yes">
    <cfargument name="meeting" type="numeric" required="yes">
    <cfset fncResults = structNew()>

    <cfquery name="addSchedule" datasource="Test">
        Insert Into Schedule(Date, Start, Eend, meeting)
        Values (<cfqueryparam cfsqltype="cf_sql_date" maxlength="10" value="#arguments.datepicker#">,
                <cfqueryparam cfsqltype="cf_sql_time" maxlength="10" value="#arguments.stime#">,
                <cfqueryparam cfsqltype="cf_sql_time" maxlength="10" value="#arguments.etime#">,
                <cfqueryparam cfsqltype="cf_sql_integer" maxlength="2" value="#arguments.meeting#">);
    </cfquery>
<cfreturn fncResults>
</cffunction>
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • Well, if you do not want the full range, why not create multiple records with the correct timespans? Hard to be more specific without seeing your query code. – Leigh Dec 08 '15 at 03:38
  • How I can create timespan for each record? Also I was thinking that would be a lot of records in my table. – espresso_coffee Dec 08 '15 at 03:43
  • (Edit) Why is that an issue? It is what databases are designed for. What exactly is the purpose of the table? *How I can create timespan for each record?* We need to see your query code. Please [edit your question](http://stackoverflow.com/posts/34143247/edit). – Leigh Dec 08 '15 at 04:46
  • I updated the question. – espresso_coffee Dec 08 '15 at 05:07
  • You forgot the query code. (Edit) I have to head out, but will check back tomorrow if no one is able to assist in the mean time. – Leigh Dec 08 '15 at 05:09
  • I think everything should be up there now. Thanks for help. I will keep working on it. – espresso_coffee Dec 08 '15 at 05:17
  • From the code, it looks like you are already generating multiple time spans. The "Save" code just is not sending that information to the server. You simply need to modify the JS code (not shown), to submit the necessary information. Whether the form should submit two (2) ranges or twenty (20) really depends on the application. As I mentioned on your other thread, you may want to take a step back and ensure the database is designed properly to support the needs of your application (reporting, modifying schedules, etcetera). – Leigh Dec 08 '15 at 15:12

0 Answers0