Ad:

ColdFusion Discussion Thread
Unsolved
Views: 3901

May 22nd, 2009

Coldfusion search multiple words.

Expand Post »

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:

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.

Similar Threads

Count given word in Python
Search from multiple tables ?? in PHP
Search multiple words using Find method in VB.NET
MySql Search using multiple input. in MySQL
swahili english sentence translator in Visual Basic 4 / 5 / 6

pirula

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Apr 2009

Permalink

May 27th, 2009

Re: Coldfusion search multiple words.

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?

gdmprog1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2009

Permalink

May 27th, 2009

Re: Coldfusion search multiple words.

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.

pirula

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Apr 2009

Permalink

Oct 30th, 2009

Re: Coldfusion search multiple words.

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

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)

kg_ally

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Mar 2009

Permalink

Feb 11th, 2010

Re: Coldfusion search multiple words.

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.

ColdFusion Syntax (Toggle Plain Text)

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

Hope that helps.

Last edited by grungy coder; Feb 11th, 2010 at 7:30 am.

grungy coder

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Feb 2010

Permalink

Feb 11th, 2010

Re: Coldfusion search multiple words.

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

ColdFusion Syntax (Toggle Plain Text)
<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#" />
<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.

samaru

Team Colleague

Reputation Points: 262

Solved Threads: 18

a.k.a inscissor

Offline

1,227 posts
since Feb 2002

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ColdFusion Forum Timeline: dynamic form variables

Next Thread in ColdFusion Forum Timeline: coldfusion merge to word

DaniWeb Message

Cancel Changes

User Name
Password