Creating a Better Search Form

[eyosan]

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

[Dan_Bracuk]

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.