Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7223
  • Last Modified:

ColdFusion + Javascript - 2 Dynamic Drop Down Boxes

Hi,

I have two tables, one is countries and the other is counties.  

I need the first drop down box to list all the countries, which it does :)  The second one needs to be dynamically populated depending on the results of the first one.  Thing is I want to do it without a page reload and just cant get it to work :(

<cfquery NAME="pull_country" DATASOURCE="#application.appdsn#">
SELECT country_id, country
FROM countries
ORDER BY country
</cfquery>

<cfquery NAME="pull_locations" DATASOURCE="#application.appdsn#">
SELECT county_id, county
FROM counties
ORDER BY county
</cfquery>

<select name="country" class="formobjects" id="location">
<option value="0">Please Select</option>
<cfoutput QUERY="pull_country">
<option value="#country_id#">#country#</option>
</cfoutput>
</select>

Any suggestions?
0
SwitchedOnMedia
Asked:
SwitchedOnMedia
  • 3
  • 2
  • 2
  • +2
1 Solution
 
RCorfmanCommented:
Here is another EE topic with the same topic in the coldfusin forum.
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21536325.html
0
 
SwitchedOnMediaAuthor Commented:
Urgh, they are all messy and lead on to about 3 other posts.  Is there actually a post with the code in to do it in ColdFusion anywherE?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
RCorfmanCommented:
Sorry, the CF Link actually has links to three methods. None of these are trivial.
Here is the Javascript method:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21268362.html
--- it actually has links to CF custom tag code also that should simplify this, but I didn't dig into it...

Here is a link to a method drawn up by mrichmon that shows how to submit queries in the background to dynamically change the lists
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20936125.html

The last method that is mentioned is to use CFAJAX, but they don't really provide details on how to do this specifically.
http://www.indiankey.com/cfajax/
I believe it is related to this method. http://www.indiankey.com/cfajax/examples/list.htm

What you are wanting is not trivial and is going to take a lot of code. It doen't make sense to try and paste all that code into another link and the best that is going to happen is for someone just to copy code from one of these other links...
0
 
pmascariCommented:
I wrote this a while ago so it's not pretty, but it got the job done.  I modified it a little to try and fit your request so forgive any typos :-)

<cfset CountyQry = QueryNew("CountyID,County_name,CountryID","Integer,varchar,integer")>
<cfoutput query="counties">
  <CFSET QueryAddRow(CountyQry,counties.currentrow)>
  <Cfset QuerySetCell(CountyQry,"CountyID",counties.ID)>
  <Cfset QuerySetCell(CountyQry,"County_name",counties.NAme)>
  <Cfset QuerySetCell(CountyQry,"CountryID",counties.CountryID)>
</cfoutput>

<script type="text/javascript">
function chgcounty(SelCountry){
var SelCounty;
var SelCountry;
SelCounty = document.myformname.counties;
<cfloop query="pull_country">
if(SelCountry == "#pull_country.country_id#"){
SelCounty.options.length = countyQry.recordcount#+1;
<Cfquery dbtype="query" name="cnty">
      SELECT CountyID,County_name,CountryID
      FROM CountyQry
      WHERE CountryID = #pull_country.country_id#
      ORDER BY County_Name
</CFQUERY>
<cfloop query="cnty">
  SelCounty.options[cnty.currentrow] = new Option("#cnty.county_name#","#cnty.CountyID#");
</cfloop>
}
</cfloop>
}
</script>

<select name="countries" onchange="chgcounty(this.options[this.selectedIndex].value">
      <cfloop query="pull_country">
        <option value="#pull_country.id#">#pull_country.Country_Name#
      </cfloop>
</select>

<select name="counties">
      <option value="">
</select>


I took some liberties, like assuming your County DB table has the country ID, assuming the name of your FORM, etc...

May need a little configuring on your side but should help.

Good luck
0
 
pmascariCommented:
Oops, just noticed my own typos...

First, the whole of the JAvaScript should be in a set of CFOUTPUT tags and this line:
SelCounty.options[cnty.currentrow] = new Option("#cnty.county_name#","#cnty.CountyID#");

should be:

SelCounty.options[#cnty.currentrow#] = new Option("#cnty.county_name#","#cnty.CountyID#");

Good luck
0
 
mmc98dl1Commented:
I would suggest the tried and tested cf_twoselectsrelated - you can see it in action at jobs.manpower.com.au

You can get the code at http://projects.nateweiss.com/nwdc/downloads/cf/CF_TwoSelectsRelated.zip 
0
 
SwitchedOnMediaAuthor Commented:
I'm trying to use your code but I get this error ...

Error Occurred While Processing Request  
There is no column in the query object.  
 
Here's the code:

<cfquery NAME="pull_country" DATASOURCE="#application.appdsn#">
SELECT country_id, country
FROM countries
ORDER BY country
</cfquery>

<cfquery NAME="pull_locations" DATASOURCE="#application.appdsn#">
SELECT county_id, county, country_id
FROM counties
ORDER BY county
</cfquery>

<cfset CountyQry = QueryNew("county_id,county,country_id","Integer,varchar,integer")>
<cfoutput query="pull_locations">
  <CFSET QueryAddRow(CountyQry,pull_locations.currentrow)>
  <Cfset QuerySetCell(CountyQry,"CountyID", county_id)>
  <Cfset QuerySetCell(CountyQry,"County_name", county)>
  <Cfset QuerySetCell(CountyQry,"CountryID", country_id)>
</cfoutput>


<CFOUTPUT>
<script type="text/javascript">
function chgcounty(SelCountry){
var SelCounty;
var SelCountry;
SelCounty = document.myformname.counties;
<cfloop query="pull_country">
if(SelCountry == "#pull_country.country_id#"){
SelCounty.options.length = #countyQry.recordcount#+1;
<Cfquery dbtype="query" name="cnty">
     SELECT CountyID,County_name,CountryID
     FROM CountyQry
     WHERE CountryID = #pull_country.country_id#
     ORDER BY County_Name
</CFQUERY>
<cfloop query="cnty">
  SelCounty.options[#cnty.currentrow#] = new Option("#cnty.county_name#","#cnty.CountyID#");
</cfloop>
}
</cfloop>
}
</script>

<select name="countries" onchange="chgcounty(this.options[this.selectedIndex].value">
     <cfloop query="pull_country">
       <option value="#pull_country.country_id#">#pull_country.country#</option>
     </cfloop>
</select>

<select name="counties">
     <option value="">
</select>
</CFOUTPUT>
0
 
Mr_NilCommented:
Take a look at the tag that mmc98dl1 suggests - Nate Weiss's two selects related is a very clean simple solution to this problem.

All you need do is write a JOINed query of your two tables, suitably GROUPed and pass it into Nate's tag.  The attributes on the tag allow you to simply chose which type of select you need as well as any other details you need to set for the selects.

If you want to write this yourself, use the CFWDDX tag to get from query from CF into an array in JS.

This JS function should help you populate any given select.

<script language='JavaScript' type="text/javascript">
function fillnextdropdown(default_value,default_text,selected_value,formname,fieldname,queryname,ParentID,ChildID,dspColumn){
      // with == in the form
      with (eval("document."+formname)){
            if (selected_value != 0) {
            // If a real selection has been made
            // Set up the defaults and clear the Activity Year Options
                  NewOpt = new Option;
                  var OptionCount = 0;
                  thisField = eval(fieldname);
                  thisQuery = eval(queryname);
                  thisParentIDCol = eval("thisQuery."+ParentID);
                  thisChildIDCol = eval("thisQuery."+ChildID);
                  thisdspColumn = eval("thisQuery."+dspColumn);
                  RowCount = thisParentIDCol.length;
                  thisField.options.length = 0;
            // Add in a new top option
                  NewOpt.value = 0;
                  NewOpt.text = default_text;
                  thisField.options[OptionCount] = NewOpt;
            // Now loop through the query we converted to a couple of arrays and output the appropriate options
                  for (var i = 0; i < RowCount; i++){
                        if (thisParentIDCol[i] == selected_value){
                              OptionCount++;
                              NewOpt = new Option;
                              NewOpt.value = thisChildIDCol[i];
                              NewOpt.text = thisdspColumn[i];
                              thisField.options[OptionCount] = NewOpt;
                              if (NewOpt.value == default_value)
                                    thisField.selectedIndex = OptionCount;
                        }
                  }
            }
      }
}
</script>

Here is an example of a call of this function.

<select name="SubGroupID" class="formstyleblue" onchange="fillnextdropdown(<cfoutput>#attributes.SubGroupID#</cfoutput>,'Select an email template >>',this[this.selectedIndex].value,'MassMailForm','TemplateID','subgrouptemplates','subgroupid','templateid','templatetitle')">
   <option value="0" class="bodytext">Select a Template Subscription Group >></option>
   <cfoutput query="SubGroupTemplates" group="SubGroupID">
         <option value="#SubGroupTemplates.SubGroupID#" class="bodytext">#SubGroupTemplates.Title#</option>
   </cfoutput>
</select>
<select name="TemplateID" class="formstyleblue">
     <option value="0">Select a Subscription Group from the list above</option>
</select>


These lines need to be included in the page to allow CFWDDX to create the JS array from the query for this example:
<script language='JavaScript' src='/cfide/scripts/wddx.js' type="text/javascript"></script>
<script language="JavaScript" type="text/javascript">
<cfwddx action="cfml2js" input="#SubGroupTemplates#" toplevelvariable="subgrouptemplates">
</script>

And this is the query that is used to populate the first select and the JS array

<cfquery name="SubGroupTemplates" datasource="#request.dsn#">
SELECT E.SubGroupID, E.Title, ET.TemplateID, CONCAT(ET.TemplateTitle,' (',ET.TemplateType,')') AS TemplateTitle
FROM EmailSubGroup E
  INNER JOIN EmailTemplate ET ON E.SubGroupID=ET.SubGroupID
ORDER BY E.Title, ET.TemplateTitle
</cfquery>

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now