Prev: CFMAIL and FORM submit
Next: drop down and depending on the selection a email is sent
From: eyosan on 12 Apr 2008 12:03
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

From: Dan Bracuk on 12 Apr 2008 13:11
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.



 | 
Pages: 1
Prev: CFMAIL and FORM submit
Next: drop down and depending on the selection a email is sent