Re: Mutli-Word search and searching each word
I think this should work... cfquery name=search datasource=search Select CompanyTypes.CompanyTypeID, CompanyTypes.CompanyTypeName, CompanyTypes.CompanyTypeDesc, CompanyTypes.CompanyTypeImage, Companies.CompanyID, Companies.CompanyName, Companies.ManagerName, Companies.SalesManagerName, Companies.CompanyCity, Companies.CompanyAdd, Companies.CompanyDesc, Companies.CompanyRank, Companies.CompanyTypeID, Products.ProductID, Products.ProductName, Products.ProductDesc, Products.ProductImage, Products.ProductTypeID, Companies_Products.Companies_ProductsID, Companies_Products.CompanyID, Companies_Products.ProductID From CompanyTypes, Companies, Products, Companies_Products Where ( CompanyTypes.CompanyTypeID = Companies.CompanyTypeID And Companies_Products.CompanyID = Companies.CompanyID And Companies_Products.ProductID = Products.ProductID ) AND ( cfset listpos = 1 cfloop index=URL.SEARCH list=#FORM.SEARCHFORM# delimiters= (Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' ) cfif listpos neq listlen(FORM.SEARCHFORM, ) OR /cfif cfset listpos = listpos +1 /cfloop ) ORDER BY Companies.CompanyTypeID, Companies.CompanyRank ASC, Companies.CompanyName ASC, Companies.CompanyID, Products.ProductID; /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297994 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Mutli-Word search and searching each word
I need to make a search function for my site. I wonder if it is possible to search for multi-word combinations which it searches each word individually and the words get sepereated by the user by SPACE . I wrote something using cfloop but it doesn't work. Please tell me what's wrong with my code. If you know a better way to make a search function that users can actually write several words in the search field and the search function searches for each word and ignores the space in between. Thanks Benign This is my code: cfquery name=search datasource=search Select CompanyTypes.CompanyTypeID, CompanyTypes.CompanyTypeName, CompanyTypes.CompanyTypeDesc, CompanyTypes.CompanyTypeImage, Companies.CompanyID, Companies.CompanyName, Companies.ManagerName, Companies.SalesManagerName, Companies.CompanyCity, Companies.CompanyAdd, Companies.CompanyDesc, Companies.CompanyRank, Companies.CompanyTypeID, Products.ProductID, Products.ProductName, Products.ProductDesc, Products.ProductImage, Products.ProductTypeID, Companies_Products.Companies_ProductsID, Companies_Products.CompanyID, Companies_Products.ProductID From CompanyTypes, Companies, Products, Companies_Products Where ( CompanyTypes.CompanyTypeID = Companies.CompanyTypeID AndCompanies_Products.CompanyID = Companies.CompanyID AndCompanies_Products.ProductID = Products.ProductID ) AND ( cfloop index=URL.SEARCH list=FORM.SEARCHFORM delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' /cfloop ) ORDER BY Companies.CompanyTypeID, Companies.CompanyRank ASC, Companies.CompanyName ASC, Companies.CompanyID, Products.ProductID; /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297923 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Mutli-Word search and searching each word
cfloop index=URL.SEARCH list=FORM.SEARCHFORM delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' /cfloop Lets say that the there are 2 words in the search technologies and safety. This loop would produce: Products.ProductName LIKE '%technologies%' OR Companies.CompanyName LIKE '%technologies%' Products.ProductName LIKE '%safety%' OR Companies.CompanyName LIKE '%safety%' Notice between the two groups there is a missing 'OR'. William -- William E. Seiter Have you ever read a book that changed your life? Go to: www.winninginthemargins.com Enter passkey: goldengrove Web Developer / ColdFusion Programmer http://William.Seiter.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297931 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Mutli-Word search and searching each word
Your list is missing #'s list=FORM.SEARCHFORM Should be list=#FORM.SEARCHFORM# The following will fix the OR issue. Notice the 1=0 at the end which will return zero results. cfloop index=URL.SEARCH list=#FORM.SEARCHFORM# delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' OR /cfloop 1=0 --- This will return: Products.ProductName LIKE '%technologies%' OR Companies.CompanyName LIKE '%technologies%' OR Products.ProductName LIKE '%safety%' OR Companies.CompanyName LIKE '%safety%' OR 1=0 On Feb 1, 2008 10:15 AM, Ali [EMAIL PROTECTED] wrote: I need to make a search function for my site. I wonder if it is possible to search for multi-word combinations which it searches each word individually and the words get sepereated by the user by SPACE . I wrote something using cfloop but it doesn't work. Please tell me what's wrong with my code. If you know a better way to make a search function that users can actually write several words in the search field and the search function searches for each word and ignores the space in between. Thanks Benign This is my code: cfquery name=search datasource=search Select CompanyTypes.CompanyTypeID, CompanyTypes.CompanyTypeName, CompanyTypes.CompanyTypeDesc, CompanyTypes.CompanyTypeImage, Companies.CompanyID, Companies.CompanyName, Companies.ManagerName, Companies.SalesManagerName, Companies.CompanyCity, Companies.CompanyAdd, Companies.CompanyDesc, Companies.CompanyRank, Companies.CompanyTypeID, Products.ProductID, Products.ProductName, Products.ProductDesc, Products.ProductImage, Products.ProductTypeID, Companies_Products.Companies_ProductsID, Companies_Products.CompanyID, Companies_Products.ProductID From CompanyTypes, Companies, Products, Companies_Products Where ( CompanyTypes.CompanyTypeID = Companies.CompanyTypeID AndCompanies_Products.CompanyID = Companies.CompanyID AndCompanies_Products.ProductID = Products.ProductID ) AND ( cfloop index=URL.SEARCH list=FORM.SEARCHFORM delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' /cfloop ) ORDER BY Companies.CompanyTypeID, Companies.CompanyRank ASC, Companies.CompanyName ASC, Companies.CompanyID, Products.ProductID; /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297934 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Mutli-Word search and searching each word
On Feb 1, 2008 9:57 PM, Gerald Guido [EMAIL PROTECTED] wrote: Your list is missing #'s list=FORM.SEARCHFORM Should be list=#FORM.SEARCHFORM# The following will fix the OR issue. Notice the 1=0 at the end which will return zero results. cfloop index=URL.SEARCH list=#FORM.SEARCHFORM# delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' OR /cfloop 1=0 --- This will return: Products.ProductName LIKE '%technologies%' OR Companies.CompanyName LIKE '%technologies%' OR Products.ProductName LIKE '%safety%' OR Companies.CompanyName LIKE '%safety%' OR 1=0 I did as you said: But it didn't work! I copied the edited code below. I put ## around list=#FORM.SEARCHFORM# and I put the 1=0 to stop the missing OR from happening. It stills returns nothing even when I type one word!!! Please help me. Thanks Benign cfparam name=FORM.SEARCHFORM type=any default= cfparam name=url.search default= type=any cfquery name=search datasource=search Select CompanyTypes.CompanyTypeID, CompanyTypes.CompanyTypeName, CompanyTypes.CompanyTypeDesc, CompanyTypes.CompanyTypeImage, Companies.CompanyID, Companies.CompanyName, Companies.ManagerName, Companies.SalesManagerName, Companies.CompanyCity, Companies.CompanyAdd, Companies.CompanyDesc, Companies.CompanyRank, Companies.CompanyTypeID, Products.ProductID, Products.ProductName, Products.ProductDesc, Products.ProductImage, Products.ProductTypeID, Companies_Products.Companies_ProductsID, Companies_Products.CompanyID, Companies_Products.ProductID From CompanyTypes, Companies, Products, Companies_Products Where ( CompanyTypes.CompanyTypeID = Companies.CompanyTypeID AndCompanies_Products.CompanyID = Companies.CompanyID AndCompanies_Products.ProductID = Products.ProductID ) AND ( cfloop index=URL.SEARCH list=#FORM.SEARCHFORM# delimiters= Products.ProductName LIKE '%#URL.SEARCH#%' OR Companies.CompanyName LIKE '%#URL.SEARCH#%' /cfloop 1=0 ) ORDER BY Companies.CompanyTypeID, Companies.CompanyRank ASC, Companies.CompanyName ASC, Companies.CompanyID, Products.ProductID; /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297945 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Mutli-Word search and searching each word
On Feb 1, 2008 9:57 PM, Gerald Guido [EMAIL PROTECTED] wrote: Thanks Benign I usually set a var before my loop for a counter then in the loop around the OR I do a CFIF to check that counter against the listlen cfset listpos = 1 cfloop list=#mylist# index=searchme (field LIKE '%#searchme#%') cfif listpos neq listlen(deptlist) OR /cfif cfset listpos = listpos +1 /cfloop ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297965 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Mutli-Word search and searching each word
On Feb 2, 2008 12:35 AM, Jeremy Keith [EMAIL PROTECTED] wrote: On Feb 1, 2008 9:57 PM, Gerald Guido [EMAIL PROTECTED] wrote: Thanks Benign I usually set a var before my loop for a counter then in the loop around the OR I do a CFIF to check that counter against the listlen cfset listpos = 1 cfloop list=#mylist# index=searchme (field LIKE '%#searchme#%') cfif listpos neq listlen(deptlist) OR /cfif cfset listpos = listpos +1 /cfloop I'm sorry I did't get the whole code login. Can you please give me a clue where I should put it Can you please tell me more about it. I know it is too much but I really apprecite it if you put some of it around my origial code which has may ORs ad these really got me confused! Thanks Benign ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4