Exercise 6: Dynamically populating the list of event types

Currently, the event types in the tripedit.cfm file are hard-coded. In the preceding exercises, each option tag in the event type contained a value attribute and option text, such as Surfing. These values come from the eventtypes table; they do not have to be hard-coded. The event type in the Trips table is an identifier that is used as a foreign key to the eventtypes table, which holds the actual event names. The eventtypes table column eventTypeID is used for the value attribute and the eventType for the literal value that appears in the select box. To retrieve the data from this table, you must include the following cfquery:

<cfquery name="GetEvents" datasource="CompassTravel">
	SELECT eventType, eventTypeID 
	FROM eventtypes				
</cfquery>

To exploit the query in the HTML option tags, you can replace the HTML select tag with a cfselect tag.

The cfselect tag is an improved version of the HTML select tag. Like other ColdFusion form tags, the cfselect tag provides the required and message attributes that validate the data entered. Using the cfselect tag and the preceding cfquery, you can implement the eventType field data entry as follows.

To display a list of event types from the eventtypes table and add validation:

  1. View the tripedit.cfm page in a browser. Select the event types drop-down list. Notice that only three event types appear in the list.
  2. Open the tripedit.cfm file in the my_app directory.
  3. Add the following code before the <html> tag:
    <cfquery name="GetEvents" datasource="CompassTravel">
    	SELECT eventType, eventTypeID 
    	FROM eventtypes				
    </cfquery>
    

    Note: In previous exercises, you learned to write structured reusable code by placing queries in ColdFusion components. Although this practice is advisable when creating ColdFusion applications, you put the query in the tripedit.cfm file for simplicity.

  4. Replace the following eventtypes code lines:
    <cfselect size="1" name="eventType" required="Yes" 
    message="Type of event must be selected."> <option value="1" selected>Surfing</option> <option value="2">Mountain Climbing</option> <option value="3">Mountain Biking</option> </cfselect>

    with these lines:

    <cfselect size="1" name="eventType" required="Yes" 
    message="Type of event must be selected."> <cfoutput query="GetEvents"> <option value="#GetEvents.eventTypeID#"> #GetEvents.eventType# </option> </cfoutput> </cfselect>
  5. Save the file.

To test the modified code:

  1. View the tripedit.cfm page in a browser.
  2. Select the event types drop-down list. Notice that all seven event types appear in the list.