search form advice

You appear to be trying to write your code before determining what it's supposed to accomplish.

Yes, the if/else logic was completely omitted.

"like" is used in the query because "%" is a wildcard. I think it can assume any value in the database.

This sample line of code, where "%" is entered as the option indicating that the user would *not* like to search by the given parameter, will return all of the records.

AND quotes.frn_CohortID like '#Form.CohortID#'

I'm not sure if this is the correct way to do it, though!

I don't see any if/else logic that deals with the non-selected selects.

Also, why are you using like instead of = in your query?

Hi everybody,

Thank you for helping me with this. The search form and query below enable the user to search by any combination of parameters based on their selections in the drop-down menus.

I had 5 parameters, so things were getting a little out of control with the cfif statements. A friend suggested that I use '%' instead, since % is a wildcard and matches everything.

In the query below, I replaced "=" with "like" to accommodate the usage of %.

Does it make sense to use %? I'm wondering if this might cause any problems for one reason or another. So far, the search form is working exactly as intended, and it avoids using many cfif statements.

Thank you,
Luke

Here is the search form, which references different queries to populate the drop-down menus. "%" is entered for the "Do not search by..." option.

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

<br><br>

Location:
<select name="locationID">
<option value="%">Do not search by location</option>
<cfoutput query="getlocs">
<option value="#getlocs.locationid#">#location#</option>
</cfoutput>
</select>
<br><br>

Cohort:
<select name="cohortID">
<option value="%">Do not search by cohort</option>
<cfoutput query="getcoh">
<option value="#getcoh.cohortid#">#cohortname#</option>
</cfoutput>
</select>
<br><br>

Category:
<select name="categoryID">
<option value="%">Do not search by category</option>
<cfoutput query="getcats">
<option value="#getcats.categoryid#">#category#</option>
</cfoutput>
</select>
<br><br>

Theme:
<select name="themeID">
<option value="%">Do not search by theme</option>
<cfoutput query="getthemes">
<option value="#getthemes.themeid#">#theme#</option>
</cfoutput>
</select>
<br><br>

Subtheme:
<select name="subthemeID">
<option value="%">Do not search by subtheme</option>
<cfoutput query="getsubs">
<option value="#getsubs.subthemeid#">#subtheme#</option>
</cfoutput>
</select>
<br><br>

<!-- reset button -->
<INPUT TYPE="Reset" NAME="ResetForm" VALUE="Clear Form">

<!-- submit button -->
<INPUT TYPE="Submit" NAME="SubmitForm" VALUE="Submit">
</FORM>


Here is the query:

<CFQUERY NAME="query" DATASOURCE="datasource">
SELECT *
FROM quotes, cohorts, themes, categories, subthemes, locations
Where cohorts.cohortid = quotes.frn_cohortid
and themes.themeid = quotes.frn_themeid
and locations.locationid = quotes.frn_locationid
and categories.categoryid = quotes.frn_categoryid
and subthemes.subthemeid = quotes.frn_subthemeid
AND quotes.frn_CohortID like '#Form.CohortID#'
AND quotes.frn_ThemeID like '#Form.ThemeID#'
AND quotes.frn_locationID like '#Form.locationID#'
AND quotes.frn_categoryID like '#Form.categoryID#'
AND quotes.frn_subThemeID like '#Form.subThemeID#'
</CFQUERY>

It depends on you want your query to resemble

select stuff
from somewhere
where this
and that
and something else

or
select stuff
from somewhere
where this
or that
or something else

Put a dummy entry at the top of each select and only search on the ones that have been changed.