Coldfusion search multiple words.

[pirula]

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:

<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.

[gdmprog1]

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?

[pirula]

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.

[kg_ally]

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

<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.

[grungy coder]

You can try to find some Capitalization method from the database that you are using and search it like.
In postgreSQL they have initcap() method for capitalizing the first letters of the words. I don't really know but I did not find any method in coldfusion that converts the first letter of a set of words in uppercase. so that's how I did manage it.

and initcap(column_name) LIKE initcap('%#url.name#%')

Hope that helps.

[samaru]

Word order I assume counts? (For example "Adobe ColdFusion" vs "ColdFusion Adobe.") Also, you may want to use <cfqueryparam> to help against SQL Injections.

<cfset searchString = "Immigration Law" />

<cfquery name="Info" datasource="YourDB">
	SELECT * 
	FROM Areas
	WHERE      
	<cfloop list="#searchString#" delimiters=" " index="word">	
  
	  ShortName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" />	OR 
      
	</cfloop>
  
  (1 <> 1)
</cfquery>

<cfdump var="#Info#" />

If you have to search a lot of text, I suggest you use your database's FTS services or Verity.