A question that comes up pretty frequently on forums is how to pre-select a set of form fields on some type of edit page. A typical example being something like this:
"I have an entry form that allows users to select multiple options from a series of checkboxes. When the form is submitted, the user's selections are saved to a database table. Now on my edit page I need to display the same set of checkboxes, but I would like to mark the user's previous selections as checked."
There are several ways to do this, and different reasons you might use each one. However, one option that is often overlooked is to use an outer join to generate the information, rather than two separate queries. Let us say you have two tables: one containing the options and the other the selections of each user.
You could use an outer join and the query would return all records from the Languages table, regardless of whether the user had selected that record or not. With the addition of a small CASE statement, the query could also include a column indicating which options were selected for the current user.
<cfquery name="getLanguages" datasource="#dsn#">
CASE WHEN fl.LanguagesID IS NULL THEN 0 ELSE 1 END AS IsSelected
FROM Languages l LEFT OUTER JOIN FavoriteLanguages fl
ON l.LanguagesID = fl.LanguagesID
AND fl.UserID = <cfqueryparam value="#session.userID#" cfsqltype="cf_sql_integer" />
Then all that is needed is to use the column value to set the initial state of the checkboxes. Voila, the user's previous selections are now checked.
<cfform method="post" action="somePage.cfm" width="400">
<b>Select your favorite languages</b></br>
checked="#IsSelected#" /> #LanguagesTitle# <br/>
<cfinput type="submit" name="saveThis" value="Submit" />
I would not say this technique is suitable for every case. But it is something to keep in mind when working with forms.