• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Creating a Better Search Form

New Here ,
Apr 12, 2008 Apr 12, 2008

Copy link to clipboard

Copied

Hi,

I'm trying to figure out how to create a dynamic search form using Coldfusion 8.

Right now if I enter multiple words in my search field the results omit certain records. For example, if I put "toy blue" in the search field it will return results that only have "TOY BLUE" together and omits any records that only have "TOY" or only have "Blue" in the column being searched.

Is there a way to add the following abilities to my basic search code located at the bottom of my post:

- Search multiple words that are entered in the criteria input field.
- The words can be separated by commas, spaces, or the word "and".
- Have it so you can search multiple word phrases by using quotes.

<!--- Search Form--->

<form name="myForm" method="post" action="results.cfm">
<p>
<input name="criteria" type="text" id="criteria">
<input type="submit" name="Submit" value="Submit">
</p>
</form>

<!--- Results Page --->

<cfquery name="qSearch" datasource="myDatabase" dbtype="ODBC">
SELECT itemID, itemName
FROM myTable
WHERE 1 = 1

<cfif IsDefined("form.criteria") AND Len(Trim(form.criteria))>
and (itemName LIKE '%#form.criteria#%' or itemDescription LIKE '%#form.criteria#%')
</cfif>

</cfquery>

<p><b>Results:</b></p>
<cfoutput query="qSearch">
<p>#itemid# - #itemName#</p>
</cfoutput>

<!--------->

Any help would be greatly appreciated.

Thanks

-C
TOPICS
Advanced techniques

Views

243

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 12, 2008 Apr 12, 2008

Copy link to clipboard

Copied

LATEST
This is the relevent sql
and (itemName LIKE '%#form.criteria#%' or itemDescription LIKE '%#form.criteria#%')

substituting the variable, it looks like this
and (itemName LIKE '%TOY BLUE%' or itemDescription LIKE '%TOY BLUE%')

You have to treat form.criteria as a list, and loop through it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation