Dynamically Populating Select Boxes

During the last procedure, you hard-coded a form's select box options.

Instead of manually entering department information on a form, you can dynamically populate a select box with database column fields. When you code this way, changes that you make to a database are automatically reflected on the form page.

To dynamically populate a select 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.

Usage example

This code retrieves department data for a form page and stores it in the GetDepartments query variable:

<CFQUERY NAME="GetDepartments" DATASOURCE="HRApp">
    SELECT     Department_Name
    FROM     Departments
</CFQUERY>

GetDepartments is the query name.
The query uses the HRApp data source to connect to the database.
The data is retrieved from the Department_Name column in the Departments table.

This code dynamically populates the Department_Name select box with the GetDepartments query data:

<SELECT NAME="Department_Name">
    <OPTION VALUE="All">All</OPTION>
    <CFOUTPUT QUERY="GetDepartments">
        <OPTION VALUE="#Department_Name#">
        #Department_Name#
        </OPTION>
    </CFOUTPUT>
</SELECT>

Hard-code an All option.
The CFOUTPUT block generates one select box option for each column returned from the query.

To dynamically populate a select box:

Open FormPage.cfm in HomeSite.
Build a query to retrieve the Department_Name column from the HRExpress Departments table by adding this code between the opening BODY tag and the opening FORM tag:

<CFQUERY NAME="GetDepartments" DATASOURCE="HRExpress">
    SELECT Department_Name
    FROM Departments
</CFQUERY>

Locate the select box on the form.
Change the select box NAME attribute to DEPARTMENT_NAME:

<SELECT NAME="Department_Name">

Delete all current select box OPTION blocks.
Add an OPTION block within the SELECT block so that users can select ALL departments.

<OPTION VALUE="All">All</OPTION>

Add a second OPTION block within the SELECT block that dynamically populates the select box with the GetDepartments query result set:

<CFOUTPUT QUERY="GetDepartments">
    <OPTION VALUE="#Department_Name#">
    #Department_Name#
    </OPTION>
</CFOUTPUT>

Save the page.
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.

Click here to see how the page should look at this time.

Click here to see the code behind the scenes.

Move on in this chapter to learn how to create action pages and work with form variables.

Note:

The behind the scenes code refers to a different action page than the one that you are building.

You will receive errors if you submit the form for processing at this time. You will learn about action pages and the characteristics of form variables later on in this chapter.