khagen
asked on
Drill Down Query wtih Select Box's
I am looking for an example of Dynamically making a query according to critieria choosen in sever select boxs. If a box has not selections, it is skipped. The trick here is that I need the combo boxs to requery when ever something is selected. If Select1 is "Bob", then Select Two has all of Bob's Jobs loaded for selection. If Select1 is not selected then All Jobs are loaded unless Select3 with more critieria is selected.
Anyone?
Anyone?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The following is a sample of the code from a custom tag I used to do what you want to do, but it is for searching for hotels. The first selection box is for country, then state, then city, then hotel. It is not tailored to your exact needs but I think you can get the concept from it. Note that In my actual code I get the neccessary records from the database before the sample code that I gave you.
-Scott
------START CODE--------
<!--- Start by creating javascript funtions to reload the page each time some thing is selected--->
<script language="JavaScript">
function update_for_country(select) {
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue;
}
function update_for_state(select){
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue+"&sta teID="+doc ument.my_f orm.stateI D.value;
}
function update_for_city(select){
<cfif url.stateID is not "">
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue+"&sta teID="+doc ument.my_f orm.stateI D.value+"& cityID="+d ocument.my _form.city ID.value;
<cfelse>
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue+"&cit yID="+docu ment.my_fo rm.cityID. value;
</cfif>
}
function update_for_hotel(select){
<cfif url.stateID is not "">
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue+"&sta teID="+doc ument.my_f orm.stateI D.value+"& cityID="+d ocument.my _form.city ID.value+" &HotelID=" +document. my_form.ho tel.value;
<cfelse>
top.location.href="#Attrib utes.this_ filename#? countryID= "+document .my_form.c ountryID.v alue+"&cit yID="+docu ment.my_fo rm.cityID. value+"&Ho telID="+do cument.my_ form.hotel .value;
</cfif>
}
</script>
<table>
<tr bgcolor=lightYellow >
<td>Choose Country<br> <SELECT NAME="countryID" onChange="update_for_count ry(this)">
<option value="">Choose a Country
<cfif (Attributes.display_type is "Package") or (Attributes.display_type is "Package_Price") or (Attributes.display_type is "Shipping")>
<CFLOOP query="rs_country">
<cfif countryID is "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s elected</c fif>>CLUBH OTEL
</cfif>
</CFLOOP>
<CFLOOP query="rs_country">
<cfif countryID is not "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s elected</c fif>>#Coun try#
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP query="rs_country">
<cfif CountryID is not "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s elected</c fif>>#Coun try#
</cfif>
</CFLOOP>
</cfif>
</SELECT>
</td>
<!--- If country = USA, the 'state' box, this box displayed, otherwise it won't be displayed --->
<cfif url.countryID is "53">
<td>Choose State<br> <SELECT NAME="stateID" onChange="update_for_state (this)">
<option value="">Choose a State
<CFLOOP query="rs_state">
<OPTION VALUE="#StateID#" <cfif url.StateID is not "" and url.StateID is "#StateID#">selected</cfif >>#State#
</CFLOOP>
</SELECT>
</td>
</cfif>
<td>Choose City <br><SELECT NAME="cityID" onChange="update_for_city( this)">
<option value="">Choose a City
<cfif Attributes.display_type is not "Rest" and Attributes.display_type is not "Package_Price" and Attributes.display_type is not "shipping" and Attributes.display_type is not "Package">
<cfif session.Lang is "EN">
<option value="0" <cfif url.cityID is "0">selected</cfif>>Add a City
</cfif>
</cfif>
<cfif url.stateID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_city) #">
<cfset sorted_key = "#sorted_key_array_city[Lo opCount]#" >
<cfif sorted_city[sorted_key].St ateID is "#url.stateID#" and sorted_city[sorted_key].Na me is not "">
<OPTION VALUE="#sorted_city[sorted _key].City ID#" <cfif sorted_city[sorted_key].Ci tyID is "#url.cityID#">selected</c fif>>#sort ed_city[so rted_key]. Name#
</cfif>
</CFLOOP>
<cfelseif url.countryID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_city) #">
<cfset sorted_key = "#sorted_key_array_city[Lo opCount]#" >
<cfif sorted_city[sorted_key].Co untryID is "#url.countryID#" and sorted_city[sorted_key].Na me is not "">
<OPTION VALUE="#sorted_city[sorted _key].City ID#" <cfif sorted_city[sorted_key].Ci tyID is "#url.cityID#">selected</c fif>>#sort ed_city[so rted_key]. Name#
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_city) #">
<cfset sorted_key = "#sorted_key_array_city[Lo opCount]#" >
<cfif sorted_city[sorted_key].Na me is not "">
<OPTION VALUE="#sorted_city[sorted _key].City ID#" <cfif sorted_city[sorted_key].Ci tyID is "#url.cityID#">selected</c fif>>#sort ed_city[so rted_key]. Name#
</cfif>
</CFLOOP>
</cfif>
</td>
<td>Choose Hotel <br><SELECT NAME="hotel" onChange="update_for_hotel (this)">
<option value="">Choose a Hotel
<!--- The 'Add Hotel' option only appear in the Hotel Detail Page --->
<cfif Attributes.display_type is "Hotel">
<cfif session.Lang is "EN">
<option value="0">Add Hotel
</cfif>
</cfif>
<cfif url.cityID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_hotel )#">
<cfset sorted_key = "#sorted_key_array_hotel[L oopCount]# ">
<cfif sorted_hotel[sorted_key].C ityID is "#url.cityID#" and sorted_hotel[sorted_key].A pproved is "True">
<OPTION VALUE="#sorted_hotel[sorte d_key].Hot elID#" <cfif sorted_hotel[sorted_key].H otelID is "#url.HotelID#">selected</ cfif>>#sor ted_hotel[ sorted_key ].Name#
</cfif>
</CFLOOP>
<cfelseif url.countryID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_hotel )#">
<cfset sorted_key = "#sorted_key_array_hotel[L oopCount]# ">
<cfif sorted_hotel[sorted_key].C ountryID is "#url.countryID#" and sorted_hotel[sorted_key].A pproved is "True">
<OPTION VALUE="#sorted_hotel[sorte d_key].Hot elID#" <cfif sorted_hotel[sorted_key].H otelID is "#url.HotelID#">selected</ cfif>>#sor ted_hotel[ sorted_key ].Name#
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a rray_hotel )#">
<cfset sorted_key = "#sorted_key_array_hotel[L oopCount]# ">
<cfif sorted_hotel[sorted_key].A pproved is "True">
<OPTION VALUE="#sorted_hotel[sorte d_key].Hot elID#" <cfif sorted_hotel[sorted_key].H otelID is "#url.HotelID#">selected</ cfif>>#sor ted_hotel[ sorted_key ].Name#
</cfif>
</CFLOOP>
</cfif>
</td>
</cfoutput>
</tr>
</table>
---------END CODE----------
-Scott
------START CODE--------
<!--- Start by creating javascript funtions to reload the page each time some thing is selected--->
<script language="JavaScript">
function update_for_country(select)
top.location.href="#Attrib
}
function update_for_state(select){
top.location.href="#Attrib
}
function update_for_city(select){
<cfif url.stateID is not "">
top.location.href="#Attrib
<cfelse>
top.location.href="#Attrib
</cfif>
}
function update_for_hotel(select){
<cfif url.stateID is not "">
top.location.href="#Attrib
<cfelse>
top.location.href="#Attrib
</cfif>
}
</script>
<table>
<tr bgcolor=lightYellow >
<td>Choose Country<br> <SELECT NAME="countryID" onChange="update_for_count
<option value="">Choose a Country
<cfif (Attributes.display_type is "Package") or (Attributes.display_type is "Package_Price") or (Attributes.display_type is "Shipping")>
<CFLOOP query="rs_country">
<cfif countryID is "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s
</cfif>
</CFLOOP>
<CFLOOP query="rs_country">
<cfif countryID is not "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP query="rs_country">
<cfif CountryID is not "-2">
<OPTION VALUE="#CountryID#" <cfif url.countryID is not "" and url.countryID is "#rs_country.CountryID#">s
</cfif>
</CFLOOP>
</cfif>
</SELECT>
</td>
<!--- If country = USA, the 'state' box, this box displayed, otherwise it won't be displayed --->
<cfif url.countryID is "53">
<td>Choose State<br> <SELECT NAME="stateID" onChange="update_for_state
<option value="">Choose a State
<CFLOOP query="rs_state">
<OPTION VALUE="#StateID#" <cfif url.StateID is not "" and url.StateID is "#StateID#">selected</cfif
</CFLOOP>
</SELECT>
</td>
</cfif>
<td>Choose City <br><SELECT NAME="cityID" onChange="update_for_city(
<option value="">Choose a City
<cfif Attributes.display_type is not "Rest" and Attributes.display_type is not "Package_Price" and Attributes.display_type is not "shipping" and Attributes.display_type is not "Package">
<cfif session.Lang is "EN">
<option value="0" <cfif url.cityID is "0">selected</cfif>>Add a City
</cfif>
</cfif>
<cfif url.stateID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_city[Lo
<cfif sorted_city[sorted_key].St
<OPTION VALUE="#sorted_city[sorted
</cfif>
</CFLOOP>
<cfelseif url.countryID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_city[Lo
<cfif sorted_city[sorted_key].Co
<OPTION VALUE="#sorted_city[sorted
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_city[Lo
<cfif sorted_city[sorted_key].Na
<OPTION VALUE="#sorted_city[sorted
</cfif>
</CFLOOP>
</cfif>
</td>
<td>Choose Hotel <br><SELECT NAME="hotel" onChange="update_for_hotel
<option value="">Choose a Hotel
<!--- The 'Add Hotel' option only appear in the Hotel Detail Page --->
<cfif Attributes.display_type is "Hotel">
<cfif session.Lang is "EN">
<option value="0">Add Hotel
</cfif>
</cfif>
<cfif url.cityID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_hotel[L
<cfif sorted_hotel[sorted_key].C
<OPTION VALUE="#sorted_hotel[sorte
</cfif>
</CFLOOP>
<cfelseif url.countryID is not "">
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_hotel[L
<cfif sorted_hotel[sorted_key].C
<OPTION VALUE="#sorted_hotel[sorte
</cfif>
</CFLOOP>
<cfelse>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_a
<cfset sorted_key = "#sorted_key_array_hotel[L
<cfif sorted_hotel[sorted_key].A
<OPTION VALUE="#sorted_hotel[sorte
</cfif>
</CFLOOP>
</cfif>
</td>
</cfoutput>
</tr>
</table>
---------END CODE----------
ASKER
Thanks Guys,
Just heading off to lunch. I'll be on this soon.
Just heading off to lunch. I'll be on this soon.
khagen,
The dynamic selectbox is written in totally in javascript, nothing about query from database. But this is useful when you have those confirm and static element in the select box, else you have to play with some javascript and cfm which you must have frame to play the trick.
http://www.buildcom.net/scripts/register.cfm
Let me know if you are interested on it or not. Then i can mail you some samples, currently i don't have any website for you to see becaz it's a project for my client.
sshhz
The dynamic selectbox is written in totally in javascript, nothing about query from database. But this is useful when you have those confirm and static element in the select box, else you have to play with some javascript and cfm which you must have frame to play the trick.
http://www.buildcom.net/scripts/register.cfm
Let me know if you are interested on it or not. Then i can mail you some samples, currently i don't have any website for you to see becaz it's a project for my client.
sshhz
If you mean populating a select box with a cf query
that is run from a value chosen in an earlier select
box, then that is possible.
Ie :-
select one has list of workers from DB - using cfquery
when a value is chosen in this box the second box
populates with jobs for the person we chose. -again using a
cfquery
Is that the idea?
that is run from a value chosen in an earlier select
box, then that is possible.
Ie :-
select one has list of workers from DB - using cfquery
when a value is chosen in this box the second box
populates with jobs for the person we chose. -again using a
cfquery
Is that the idea?
<Cfquery>
select * from table
</cfquery>
<cfoutput query="">
javascript genarated here so that it will work..as a client side function
</cfoutput>