Dynamically Populating List Boxes

In Chapter 4, you hard-coded a 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 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 cfoutput tag with the query attribute within the select tag to dynamically populate the options of this form control.

To dynamically populate a list box:

Open the file formpage.cfm in ColdFusion Studio.

Modify the file so that it appears as follows:

<html>

<head>

<title>Input form</title>

</head>

<body>

<cfquery name="GetDepartments" datasource="CompanyInfo">

SELECT DISTINCT Location

FROM Departmt

</cfquery>



<!--- Define the action page in the form tag. 

  The form variables will pass to this page 

  when the form is submitted --->



<form action="actionpage.cfm" method="post">



<!-- text box -->

<p>

First Name: <input type="Text" name="FirstName" size="20" maxlength="35"><br>

Last Name: <input type="Text" name="LastName" size="20" maxlength="35"><br>

Salary: <input type="Text" name="Salary" size="10" maxlength="10">

</p>



<!-- list box -->

City



<select name="City">

<cfoutput query="GetDepartments">

<option value="#GetDepartments.Location#">

#GetDepartments.Location#

</option>

</cfoutput>

</select>



<!-- radio buttons -->

<p>

Department:<br>

<input type="radio" name="Department" value="Training">Training<br>

<input type="radio" name="Department" value="Sales">Sales<br>

<input type="radio" name="Department" value="Marketing">Marketing<br>

<input type="radio" name="Department" value="HR">HR<br>

</p>



<!-- check box -->

<p>

Contractor? <input type="checkbox" name="Contractor" value="Yes" checked>Yes

</p>



<!-- reset button -->

<input type="reset" name="ResetForm" value="Clear Form">



<!-- submit button -->

<input type="submit" name="SubmitForm" value="Submit">

</form>

</body>

</html>

Save the page as formpage.cfm.
View formpage.cfm 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="CompanyInfo"> SELECT DISTINCT Location FROM Departmt </cfquery>
Get the locations of all departments in the Departmt table. The DISTINCT clause eliminates duplicate location names from the returned query results.

<select name="City"> <cfoutput query="GetDepartments"> <option value="#GetDepartments.Location#"> #GetDepartments.Location# </option> </cfoutput> </select>
Populate the City selection list from the Location column of the GetDepartments query. The control has one option for each row returned by the query.

Developing ColdFusion Applications
Making Variables Dynamic

Code	Description
<cfquery name="GetDepartments" datasource="CompanyInfo"> SELECT DISTINCT Location FROM Departmt </cfquery>	Get the locations of all departments in the Departmt table. The DISTINCT clause eliminates duplicate location names from the returned query results.
<select name="City"> <cfoutput query="GetDepartments"> <option value="#GetDepartments.Location#"> #GetDepartments.Location# </option> </cfoutput> </select>	Populate the City selection list from the Location column of the GetDepartments query. The control has one option for each row returned by the query.