coldfusion reservation system

I would think it would be not only easier to program but more user friendly to provide a calendar like interface that shows availability dates/times (and possibly unavailable dates/times grayed out).
At each step of the way show the user what's available and let them click to select and go on to the next step.

post back with your thoughts ...

This is a very vague question and it sounds as if you have a lot of design requirements to iron out. But here are some ideas to get you started.

You have to decide what type of interface you want for the instructors to choose times; calendar, drop down list, etc. If a list, populate it with a query that displays locations for the current instructor.Then it will display a car / location based on availability and ask the instructor to confirm.  

Your database should keep track of available cars for each location and the query to assign cars would look at only the cars that are unassigned during the time frame entered.

At the very least, your database would contain tables to hold Instructors, Locations, and Cars. You would most likely need bridge tables between Instructors / Locations, and Locations / Cars to make the assignments. the columns you need in each table depend upon the needs of the web site, but I generally always add some auditing fields. These usually include create date, create ID, modified date, modified ID, delete date, delete ID, and a Boolean active field.

You would also need some sort of admin interface so the cars and instructor can be assigned to locations. Plus someone will have to add / delete users and determine their access privileges.

I have already design all of that. I need to display available times in the drop dropDown from where the instructor will pick the time

ok - so forgive me but could you clarify what you are asking please ...

I need to create a time availability drop down. Based on the instructor and specified location.

well in general you could loop through your times and display based on if reserved or not
first you can query
<cfquery name="myQ">
select starttime from table
</cfquery>
so now you have a list of times that are already taken
loop from 6am to 8pm
<cfloop from="0600" to="2000" index="ii"> easier to use 24 hour time i would imagine
<option value="#ii#" <cfif listfind(valuelist(myQ.startttime),ii)> then some code that grays out and makes unselectable</cfif>>#ii#
</cfloop>

would need to do some time conversions coming out of DB probably so that query out is a list of 0700,0900,1400 etc AND need to tie in day and year I'd imagine

that of course is real quick and basic but should get you started ...

that is the approach I'd probably take _agx_ ...

Would I check for the session conflict and a car conflict. Each session may or may not allow the buffer. Because the instructor may need to travel from location to a location

so far that what I came up with

[indent]<cfloop index="ii" from="#startHour#" to="#endHour#">
							<cfloop index="jj" from="0" to="45" step="15">
								<cfset showtime="y">
								<cfset time = createtime(ii, jj, 0)>

								<cfloop query="qAssignedSessions">
									<cfset preSessionStart = dateadd("n",-120-buffertime,sessionstart)>
									<cfset postSessionend = dateadd("n",bufferTime,sessionend)>
									<cfoutput>
										#buffertime#--#preSessionStart# -- #postSessionend#<br>
									</cfoutput>
							  		<cfif (hour(time) gt hour(preSessionStart) or (hour(time) eq hour(preSessionStart) and minute(time) gt minute(preSessionStart)))
							  				and (hour(time) lt hour(postSessionend) or (hour(time) eq hour(postSessionend) and minute(time) lt minute(postSessionend)))>
								  		<cfset showtime="n">
								  		<cfbreak>
								  	</cfif>
								</cfloop>
								<cfset ArrayAppend(timeslots, timeformat(time, 'HH:mm tt'))>
								<cfif ((<!---datecompare(arguments.startDt,latestSessionDate) eq 1 and---> ((hour(time) eq 20 and minute(time) eq 00) or hour(time) lt 20))
										or (datecompare(arguments.startDt,latestSessionDate) eq 0 and ((hour(time) eq 20 and minute(time) eq 00) or (hour(time) lt 20 and hour(time) gt hour(now()))))
											)
										and showtime eq "y">
									<option value="<cfoutput>#timeformat(time, 'HH:mm')#</cfoutput>"><cfoutput>#timeformat(time, "hh:mm tt")#</cfoutput></option>
								</cfif>
							</cfloop>
						</cfloop>[/indent]

Select allOpen in new window

"the buffer" ???

when a reservation is made you update a db table - not stored in session ...

well the instructor can service multiple locations. So he/she in location A and the session is finished at 10:00 AM his next session is in location B and they are 1 mile away from each other the instructor need some time to get there. That why I am checking the buffer time so the sessions are not scheduled back to back to avoid down time

if your "sessions" take 45 minutes - then have your app create 60 minute sessions - for example ...

and that what I am doing here . The session takes 120 minus and the buffer is 15 minutes

<cfset preSessionStart = dateadd("n",-120-buffertime,sessionstart)>

so in your db table for your time blocks - like agx suggested - use 135 minute time blocks instead of the 15 minute blocks he suggested  ...
6 am
8:15 am
10:30 am
etc ...

Provided this example how would I also remove some unwanted slots.

For example The instructor A had reservation from 6 to 8. so what I want to show next is 8 and 8:15 and block out the time for 105 min, so the next available time slot will be 10:00AM.

and then contunie 10:15,1030.

And then another reservation at 1:00Pm then show 3:00PM and 3:15 and block out untill 5:00pm