Dynamically populating list boxes



The code in Creating a basic form hard-coded the form’s list box options. Instead of manually entering the information on a form, you can dynamically populate a list box with database fields. When you write code this way, the form page automatically reflects the changes that you make to the database.

You use two tags to dynamically populate a list box:

  • Use the cfquery tag to retrieve the column data from a database table.

  • Use the cfselect tag with the query attribute to dynamically populate the options of this form control.

Dynamically populate a list box

  1. Open the formpage.cfm page.

  2. Modify the file so that it appears as follows:

    <html> 
<head> 
<title>Input form</title> 
</head> 
<body> 
<cfquery name="GetDepartments" datasource="cfdocexamples"> 
    SELECT DISTINCT Location 
    FROM Departmt 
</cfquery> 
 
<!--- Define the action page in the form tag.  
    The form variables pass to this page  
    when the form is submitted ---> 
 
<cfform action="actionpage.cfm" method="post"> 
 
<!--- Text box. ---> 
<p> 
First Name: <cfinput type="Text" name="FirstName" size="20" maxlength="35"><br> 
Last Name: <cfinput type="Text" name="LastName" size="20" maxlength="35"><br> 
Salary: <cfinput type="Text" name="Salary" size="10" maxlength="10"> 
</p> 
 
<!--- List box. ---> 
City 
<cfset optsize=getDepartments.recordcount + 1> 
<cfselect name="City" query="GetDepartments" value="Location" size="#optsize#"> 
    <option value="">Select All 
</cfselect> 
 
<!--- Radio buttons. ---> 
<p> 
Department:<br> 
<cfinput type="radio" name="Department" value="Training">Training<br> 
<cfinput type="radio" name="Department" value="Sales">Sales<br> 
<cfinput type="radio" name="Department" value="Marketing">Marketing<br> 
<cfinput type="radio" name="Department" value="HR">HR<br> 
</p> 
 
<!--- Check box. ---> 
<p> 
Contractor? <cfinput type="checkbox" name="Contractor" value="Yes" checked>Yes 
</p> 
 
<!--- Reset button. ---> 
<cfinput type="reset" name="ResetForm" value="Clear Form"> 
 
<!--- Submit button. ---> 
<cfinput type="submit" name="SubmitForm" value="Submit"> 
</cfform> 
</body> 
</html>

  3. Save the page as formpage.cfm.

  4. View the formpage.cfm page in a browser.

    The changes that you just made appear in the form.

    Remember that you need an action page to submit values.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

 
<cfquery name="GetDepartments" datasource="cfdocexamples"> 
SELECT DISTINCT Location 
FROM Departmt 
</cfquery>

Gets the locations of all departments in the Departmt table. The DISTINCT clause eliminates duplicate location names from the returned query results. 

<cfset optsize=getDepartments.recordcount + 1>

Sets the optsize variable to the number of entries to add dynamically to the selection list, plus one for the manually coded Select All option.

 
<cfselect name="City" query="GetDepartments" value="Location" size="#optsize#"> 
<option value="">Select All 
</cfselect>

Populates the City selection list from the Location column of the GetDepartments query. The control has one option for each row returned by the query.

Adds an option that allows users to select all locations. If the user selects this option, the form value is an empty string. The action page must check for the empty string and handle it appropriately.