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>