ColdFusion

Coldfusion search multiple words.

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2009

Posts: 7

Reputation: pirula is an unknown quantity at this point

Solved Threads: 0

pirula

Offline

Newbie Poster

Coldfusion search multiple words.

May 22nd, 2009

Hi,

I would like to search one field in database with multiple words. Also i will allow searches for example : a or cs....(one,two characters).
My code:

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<cfparam name="URL.NAME" default="1" type="Any">
<cfquery name="search" datasource="datasource">
SELECT *
FROM search
WHERE NAME LIKE ="%#URL.NAME#%"
ORDER BY DATE DESC
</cfquery>
<cfparam name="URL.NAME" default="1" type="Any">
<cfquery name="search" datasource="datasource">
SELECT *
FROM search
WHERE NAME LIKE ="%#URL.NAME#%"
ORDER BY DATE DESC
</cfquery>

this code works fine but only with one word or if there is exact match with multiple words.
Example:
database rows:
1.adobe photohop
2.adobe photoshop cs4

search string adobe(my code) will display both results,but
search string adobe cs4 will not display results. I want if user type one,two, three words to match any of these words and to display all results like google.
What am I doing wrong? I also tried to replace characters and add + but no results.
Any idea or example i would appreciate.

•

Join Date: May 2009

Posts: 3

Reputation: gdmprog1 is an unknown quantity at this point

Solved Threads: 0

gdmprog1

Offline

Newbie Poster

Re: Coldfusion search multiple words.

May 27th, 2009

You don't explain enough about what the data looks like and the result you are trying to get. But couldn't you break the url.name into pieces based on spaces and then select LIKE for each piece?

•

Join Date: Apr 2009

Posts: 7

Reputation: pirula is an unknown quantity at this point

Solved Threads: 0

pirula

Offline

Newbie Poster

Re: Coldfusion search multiple words.

May 27th, 2009

i was tired to think when i posted question. You can use and i am using cfif and cfloop tags in cfqueries. Just loop url.name, that is simple explanation and for advanced one you will need a couple of cfif,cfset and cfloop's in cfqueries.

Last edited by pirula; May 27th, 2009 at 11:23 am.

•

Join Date: Mar 2009

Posts: 8

Reputation: kg_ally is an unknown quantity at this point

Solved Threads: 0

kg_ally

Offline

Newbie Poster

Oct 30th, 2009

you have to break your input into 2 elements separated by a delimiter(I did it with a space) and put them into an array

 Help with Code Tags 
 coldfusion Syntax (Toggle Plain Text) 
<cfparam name="strSearch" default="">
<cfparam name="myArrayList" default="">
<cfset myArrayList=ArrayNew(1)>
 
<cfif isdefined("form.searchMeals") AND form.searchMeals neq "">
	<cfset strSearch = #trim(form.searchMeals)#>
	<cfset strSearch = #lcase(strSearch)#>
	 <cfif strSearch contains ""> 
	 	<cfset strSearch = Replace(strSearch," ",",")>
		<cfset myArrayList = ListToArray(strSearch)>
		<cfif myArrayList[1] neq "">
			<cfset element1 = #myArrayList[1]#>
		</cfif>
		<cfif strSearch contains ",">
			<cfset element2 = #myArrayList[2]#>
		</cfif>
	 </cfif>
</cfif>
<cfparam name="strSearch" default="">
<cfparam name="myArrayList" default="">
<cfset myArrayList=ArrayNew(1)>

<cfif isdefined("form.searchMeals") AND form.searchMeals neq "">
	<cfset strSearch = #trim(form.searchMeals)#>
	<cfset strSearch = #lcase(strSearch)#>
	 <cfif strSearch contains ""> 
	 	<cfset strSearch = Replace(strSearch," ",",")>
		<cfset myArrayList = ListToArray(strSearch)>
		<cfif myArrayList[1] neq "">
			<cfset element1 = #myArrayList[1]#>
		</cfif>
		<cfif strSearch contains ",">
			<cfset element2 = #myArrayList[2]#>
		</cfif>
	 </cfif>
</cfif>

now you can easily pass both or either one into your query.
Hope this helps.

Last edited by peter_budo; Oct 30th, 2009 at 7:17 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)