Skip navigation
biene22
Currently Being Moderated

Chaining select lists

Feb 20, 2012 2:45 AM

Hi,

 

I try to create two dynamic  depending  selectlists whereas the second select list depends on the choose of the first list.

Both lists are populated by a database query.

 

Here is a simple example:

 

First list: Usergroups

 

<cfselect name="usergroups" >

<cflloop query="select_usergroup">

<option value="id">#usergroup_name#

</cfselect>

 

Second list: Users

<cfselect name="users" >

<cflloop query="select_user">

<option value="id">#user_name#

</cfselect>

 

When selecting a usergroup in  the first list, the query of the second list runs in dependence of the value of first list.

 

 

Is there a possibility to solve this problem without using Url-parameter? I have a big formular with many form-fields and I assume that this method is not very fast.

I also try to use data binding, but then I have a problem preselecting values in the lists.

 

 

Environment: Oracle 11g, ColdFusion 9 Standard

 

 

Any idea?

 

regards Claudia

 
Replies
  • Currently Being Moderated
    Feb 20, 2012 5:59 AM   in reply to biene22

    Example 2 in the Livedocs on cfselect is a fully worked-out example. It consists of 3 files:  a CFC (BinFons.cfc) and an XML file(states.xml) and a CFM page containing the form and cfselect tags(call it, say, formPage.cfm). All you have to do is place the files in the same directory.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 20, 2012 7:42 AM   in reply to biene22

    biene22 wrote:

     

    Yes, I know this example and it works very well.

     

    But how to preselect values in the selectboxes when loading the page for the first time?

    I get the values from a database query and have to set this values as selected in the selectboxes.

     

    Documentation said that "selected" attribute applies only if selection list items are generated from a query.

    What is the problem then?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 21, 2012 4:54 AM   in reply to biene22

    biene22 wrote:

     

    Okay, I try to make my problem more clearly by enhancing the example:

     

    I have a customer formular which I open with the  customer_id(as url parameter). In the database is a table

    where customer_id, state and city is saved.

    State and city are selectboxes. When I open the formular the selectbox should have selected the values saved

    in the customer table.

     

    <cfquery name="customer" datasource="test">

    select customer_id,state,city

    from customers

    where customer_id = #url.cust_id#

    </cfquery>

     

    ...
    ...

     

    How I get the values customer.state and customer.city as selected in the listboxes?

    ...
    ...

     

    The value url.cust_id appears to be the only choice to make. The query then tells you the customer, his state and his city. There are no more unknowns and, therefore, no need for lists.

     

    You could simply do something with text fields, like this:

     

    <cfparam name="url.cust_id" default="1234">

     

    <cfquery name="customerDetails" datasource="test">

        select custName,state,city

        from customers

        where customer_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.cust_id#">

    </cfquery>

     

    <cfform name="customerForm">

    Customer: <cfinput  type="text" name="customerName" value="#customerDetails.custName#"> <br>

    State: <cfinput  type="text" name="state" value="#customerDetails.state#"> <br>

    City: <cfinput  type="text" name="city" value="#customerDetails.city#"> <br>

    <cfinput name="sbmt" type="submit" value="Send">

    </cfform>

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 22, 2012 3:39 AM   in reply to biene22

    biene22 wrote:

     

    I don't want to have simple cfinput fields, I want selectboxes because it must be possible to change the

    values of state and city. And therefore I need this chaining listboxes.

    OK. I have a worked-out example that uses the database cfdocexamples. This database ships with ColdFusion, and comes installed as a datasource. It means you can run the example directly, without having to install anything.

     

    The example illustrates the chaining you talk about. It supposes that you know the location, and that it is passed by URL. You wish that the page give you the departments in that location. When you click on the department, you want to get the list of employees in the department.

     

    My database engine is MySQL. If yours is different, then you may have to slightly adapt some of my SQL statements. Place the 2 files in the same directory.

     

    selectEmployee.cfm

    <!--- The location is expected to come in as a URL parameter. The bind requires it when the page loads, so a default location, San Francisco, is set. --->

    <cfparam name="url.location" default="San Francisco">

     

    <cfform name="empForm">

    <cfinput name="loc" type="hidden" value="#url.location#" >

    Department: <cfselect  name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

    <br>

    Employee: <cfselect  name="emp" bind="cfc:Employee.getEmployees({dept})"></cfselect>

    <br><br>

    <cfinput name="sbmt" type="submit" value="Submit">

    </cfform>

    <div>

        Form:

        <cfdump var="#form#">

    </div>

     

     

    Employee.cfc

     

    <cfcomponent>

    <cffunction name="getDepartments" access="remote" output="false" returntype="array">

    <cfargument name="location" type="string" required="true">

    <cfset var departments = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "departments" dataSource = "cfdocexamples">

        SELECT department

        FROM employees

        WHERE location = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#">

    </cfquery>

     

    <cfset arr[1][1]="">

    <cfset arr[1][2]="Select department">

     

    <cfloop query="departments">

    <cfset arr[currentrow+1][1]=department><!---option values in the select list--->

    <cfset arr[currentrow+1][2]=department><!---displayed values in the select list --->

    </cfloop>

     

    <!--- Alternative, if you require no "Select department" option --->

    <!--- <cfloop query="departments">

    <cfset arr[currentrow][1]=department><!---option values in the select list--->

    <cfset arr[currentrow][2]=department><!---displayed values in the select list --->

    </cfloop> --->

     

    <cfreturn arr>

    </cffunction>

     

    <cffunction name="getEmployees" access="remote" output="false" returntype="any">

    <cfargument name="dept" type="string" required="true">

    <cfset var employees = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "employees" dataSource = "cfdocexamples">

        SELECT Emp_ID,  FirstName || ' ' || LastName as Name

        FROM Employees

        WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">

    </cfquery>

     

    <cfset arr[1][1]="">

    <cfset arr[1][2]="Select employee">

    <cfloop query="employees">

    <cfset arr[currentrow+1][1]=emp_id><!---option values in the select list--->

    <cfset arr[currentrow+1][2]=name><!---displayed values in the select list --->

    </cfloop>

     

    <!--- Alternative, if you require no "Select employee" option --->

    <!--- <cfloop query="employees">

    <cfset arr[currentrow][1]=emp_id><!---option values in the select list--->

    <cfset arr[currentrow][2]=name><!---displayed values in the select list --->

    </cfloop> --->

     

    <cfreturn arr>

    </cffunction>

    </cfcomponent>

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 5:20 AM   in reply to biene22

    biene22 wrote:

     

    Thanks for this example. It works fine, but my problem is:

     

    how can I open selectEmployee.cfm and set for example department "Sales" as selected in

    <cfselect  name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

    I don't understand what you mean by "open selectEmployee.cfm". Could you please explain?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 5:50 AM   in reply to biene22

    biene22 wrote:

     

    How can I run selectEmployee.cfm in browser

    and set for example department "Sales" as selected in

    <cfselect  name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

    Ah, I see. I hope you understand that, in the example I gave, the argument in getDepartment is the location, not the department. However, you can easily modify the code to use department in place of location.

     

    The first statement in selectEmployee.cfm tells you how to open the page. The URL scope means you should do it, for example, like this:

     

    http://127.0.0.1:8500/bindTest/selectEmployee.cfm?location=Newton

     

    (Remember that the path might be different on your own server.) Here, I have used the location Newton to override the default of San Francisco.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 2, 2012 8:55 AM   in reply to biene22

    Did you succeed in opening the CFM page the way I suggested? There are alternatives, of course.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 6, 2012 1:08 AM   in reply to biene22

    I see what you mean. I could also see a few minor errors, which the following suggestion corrects.

     

    selectEmployee.cfm

     

    <!--- The employee ID (emp_id) is expected to come in as a URL parameter. The bind requires it when the page loads, so a default emp_id of 7 is set. --->

    <cfparam name="url.emp_id" default="7">

     

    <cfquery name = "select_user" dataSource = "cfdocexamples">

        SELECT department, FirstName || ' ' || LastName as emp_name

        FROM employees

        WHERE emp_id = #url.emp_id#

    </cfquery>

     

    <cfform name="empForm">

    Department: <cfselect  name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>

    <br>

    Employee: <cfselect  name="emp" bind="cfc:Employee.getEmployees()" bindonload="true" selected="#select_user.emp_name#"></cfselect>

    <br><br>

    <cfinput name="sbmt" type="submit" value="Submit">

    </cfform>

     

     

    Employee.cfc

     

    <cfcomponent>

    <cffunction name="getDepartments" access="remote" output="false" returntype="array">

    <cfset var departments = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "getDept" dataSource = "cfdocexamples">

        SELECT distinct(department)

        FROM employees

    </cfquery>

     

    <cfloop query="getDept">

    <cfset arr[currentrow][1]=department><!---option values in the select list--->

    <cfset arr[currentrow][2]=department><!---displayed values in the select list --->

    </cfloop>

     

    <cfreturn arr>

    </cffunction>

     

    <cffunction name="getEmployees" access="remote" output="false" returntype="any">

    <cfset var employees = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "employees" dataSource = "cfdocexamples">

        SELECT emp_id,  FirstName || ' ' || LastName as Name

        FROM Employees

    </cfquery>

     

    <cfloop query="employees">

    <cfset arr[currentrow][1]=emp_id><!---option values in the select list--->

    <cfset arr[currentrow][2]=name><!---displayed values in the select list --->

    </cfloop>

     

    <cfreturn arr>

    </cffunction>

    </cfcomponent>

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 6, 2012 12:56 AM   in reply to biene22

    So long as we understand that what we now have is experimental. It goes against the whole point of binding, which is to enable you to separate business code (like queries) from presentation code.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 6, 2012 5:01 AM   in reply to biene22

    What do you mean by 'binding doesn't work anymore'. If you get the select lists, then binding is working.

     

    I've just run a quick test. It works, at least on my system. What's your ColdFusion version?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 6, 2012 5:40 AM   in reply to biene22

    biene22 wrote:

     

    When I change  the department, values of selectlist "employee" are always the same. They does not depend on the selected value of department.

     

    ColdFusion 9 is as up to date as you can be. You could have experimented a bit with the above code. Binding to department is just a minor variation. For example,

     

    selectEmployee.cfm

    <!--- The employee ID (emp_id) is expected to come in as a URL parameter. The bind requires it when the page loads, so a default emp_id, 7, is set. --->

    <cfparam name="url.emp_id" default="6">

     

    <cfquery name = "select_user" dataSource = "cfdocexamples">

        SELECT department, FirstName || ' ' || LastName as emp_name

        FROM employees

        WHERE emp_id = #url.emp_id#

    </cfquery>

     

    <cfform name="empForm">

    Department: <cfselect  name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>

    <br>

    Employee:   <cfselect  name="employee" bind="cfc:Employee.getEmployees({dept})" bindonload="true" selected="#select_user.emp_name#" ></cfselect>

    <br><br>

    <cfinput name="sbmt" type="submit" value="Submit">

    </cfform>

     

    Employee.cfc

    <cfcomponent>

    <cffunction name="getDepartments" access="remote" output="false" returntype="array">

    <cfset var departments = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "getDept" dataSource = "cfdocexamples">

        SELECT distinct(department)

        FROM employees

    </cfquery>

     

    <cfloop query="getDept">

    <cfset arr[currentrow][1]=department><!---option values in the select list--->

    <cfset arr[currentrow][2]=department><!---displayed values in the select list --->

    </cfloop>

     

    <cfreturn arr>

    </cffunction>

     

    <cffunction name="getEmployees" access="remote" output="false" returntype="any">

    <cfargument name="dept" type="string" required="true">

    <cfset var employees = queryNew("","")>

    <cfset var arr = arrayNew(2)>

     

    <cfquery name = "employees" dataSource = "cfdocexamples">

        SELECT emp_id,  FirstName || ' ' || LastName as emp_name

        FROM Employees

       WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">

    </cfquery>

     

    <cfloop query="employees">

    <cfset arr[currentrow][1]=emp_id><!---option values in the select list--->

    <cfset arr[currentrow][2]=emp_name><!---displayed values in the select list --->

    </cfloop>

     

    <cfreturn arr>

    </cffunction>

    </cfcomponent>

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points