Solved

Drill Down Query wtih Select Box's

Posted on 2001-06-21
6
172 Views
Last Modified: 2013-12-24
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?
0
Comment
Question by:khagen
6 Comments
 
LVL 5

Accepted Solution

by:
Yog earned 80 total points
ID: 6215515
One good example here, but thats in html page..

http://www.wondersky.com/etech/programming/javascript/code_dynamic_listbox.htm

If you can post the query , then maybe we can help how you construct a similar javscript as shown above
0
 
LVL 5

Expert Comment

by:Yog
ID: 6215535
What i mean is
<Cfquery>
  select * from table
</cfquery>

<cfoutput query="">
javascript genarated here so that it will work..as a client side function
</cfoutput>
0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 6215572
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="#Attributes.this_filename#?countryID="+document.my_form.countryID.value;                  
      }      

      function update_for_state(select){            
            top.location.href="#Attributes.this_filename#?countryID="+document.my_form.countryID.value+"&stateID="+document.my_form.stateID.value;                  
      }      
      
      function update_for_city(select){
            <cfif url.stateID is not "">            
                  top.location.href="#Attributes.this_filename#?countryID="+document.my_form.countryID.value+"&stateID="+document.my_form.stateID.value+"&cityID="+document.my_form.cityID.value;                  
            <cfelse>            
                  top.location.href="#Attributes.this_filename#?countryID="+document.my_form.countryID.value+"&cityID="+document.my_form.cityID.value;                  
            </cfif>      
      }
      
      function update_for_hotel(select){
            <cfif url.stateID is not "">
                  top.location.href="#Attributes.this_filename#?countryID="+document.my_form.countryID.value+"&stateID="+document.my_form.stateID.value+"&cityID="+document.my_form.cityID.value+"&HotelID="+document.my_form.hotel.value;                              
            <cfelse>
                  top.location.href="#Attributes.this_filename#?countryID="+document.my_form.countryID.value+"&cityID="+document.my_form.cityID.value+"&HotelID="+document.my_form.hotel.value;                  
            </cfif>
      }      

</script>      

<table>
<tr bgcolor=lightYellow >
      <td>Choose Country<br> <SELECT NAME="countryID" onChange="update_for_country(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#">selected</cfif>>CLUBHOTEL
                                                </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#">selected</cfif>>#Country#
                                                </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#">selected</cfif>>#Country#
                                                   </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_array_city)#">
                                                <cfset sorted_key = "#sorted_key_array_city[LoopCount]#">
                                           <cfif sorted_city[sorted_key].StateID is "#url.stateID#" and sorted_city[sorted_key].Name is not "">                                                
                                                <OPTION VALUE="#sorted_city[sorted_key].CityID#" <cfif sorted_city[sorted_key].CityID is "#url.cityID#">selected</cfif>>#sorted_city[sorted_key].Name#                                                      
                                          </cfif>
                                    </CFLOOP>                                                
                              <cfelseif url.countryID is not "">
                                    <CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_array_city)#">
                                                <cfset sorted_key = "#sorted_key_array_city[LoopCount]#">
                                           <cfif sorted_city[sorted_key].CountryID is "#url.countryID#" and sorted_city[sorted_key].Name is not "">                                                
                                                <OPTION VALUE="#sorted_city[sorted_key].CityID#" <cfif sorted_city[sorted_key].CityID is "#url.cityID#">selected</cfif>>#sorted_city[sorted_key].Name#                                                      
                                          </cfif>
                                    </CFLOOP>                                                                                                             
                              <cfelse>
                                    <CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_array_city)#">
                                                <cfset sorted_key = "#sorted_key_array_city[LoopCount]#">
                                           <cfif sorted_city[sorted_key].Name is not "">                                                
                                                <OPTION VALUE="#sorted_city[sorted_key].CityID#" <cfif sorted_city[sorted_key].CityID is "#url.cityID#">selected</cfif>>#sorted_city[sorted_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_array_hotel)#">
                                                <cfset sorted_key = "#sorted_key_array_hotel[LoopCount]#">
                                           <cfif sorted_hotel[sorted_key].CityID is "#url.cityID#" and sorted_hotel[sorted_key].Approved is "True">                                                
                                                <OPTION VALUE="#sorted_hotel[sorted_key].HotelID#" <cfif sorted_hotel[sorted_key].HotelID is "#url.HotelID#">selected</cfif>>#sorted_hotel[sorted_key].Name#                                                      
                                          </cfif>
                                    </CFLOOP>                                                                                                        
                                                                 
                              <cfelseif url.countryID is not "">                                                                 
                                    <CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_array_hotel)#">
                                                <cfset sorted_key = "#sorted_key_array_hotel[LoopCount]#">
                                           <cfif sorted_hotel[sorted_key].CountryID is "#url.countryID#" and sorted_hotel[sorted_key].Approved is "True">                                                
                                                <OPTION VALUE="#sorted_hotel[sorted_key].HotelID#" <cfif sorted_hotel[sorted_key].HotelID is "#url.HotelID#">selected</cfif>>#sorted_hotel[sorted_key].Name#                                                      
                                          </cfif>
                                    </CFLOOP>
                                                            
                              <cfelse>
                                    <CFLOOP INDEX="LoopCount" FROM="1" TO="#arrayLen(sorted_key_array_hotel)#">
                                                <cfset sorted_key = "#sorted_key_array_hotel[LoopCount]#">
                                           <cfif sorted_hotel[sorted_key].Approved is "True">                                                
                                                <OPTION VALUE="#sorted_hotel[sorted_key].HotelID#" <cfif sorted_hotel[sorted_key].HotelID is "#url.HotelID#">selected</cfif>>#sorted_hotel[sorted_key].Name#                                                      
                                          </cfif>
                                    </CFLOOP>                                      
                              </cfif>
      </td>
      </cfoutput>
      </tr>      

</table>


---------END CODE----------
0
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 1

Author Comment

by:khagen
ID: 6215616
Thanks Guys,
Just heading off to lunch.  I'll be on this soon.
0
 
LVL 2

Expert Comment

by:sshhz
ID: 6217120
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
0
 

Expert Comment

by:theworpler
ID: 6218813
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?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question