I created a search text field for my website but I am having a problem with putting in misspelled words and not results popping up. For example if a user types in pops it will come up with 0 results but pop comes up with results. Select * FROM tblCust Where CustKeyWords LIKE '%#form.crud#%' or CustKeyWords LIKE '%#form.crud#' or CustKeyWords LIKE '#form.crud#%' Any advise would be great!!!!!!!!! JN
My recommendation is the scrap what you are doing and utilize the verity search (if you can). My reason for that is because with your current approach you are opening yourself up to SQL Injections (NOT GOOD!) and based on your code above - YOU ARE WAY OPEN! lol. Do some research on the following tags: cfcollection, cfindex, cfsearch. These will help you AND they are much much faster. In a nutshell, what it will allow you to do is to create (if you will) an object that is searchable that is separate and apart from the database (NO MORE SQL INJECTIONS) and the engine that is used handles misspellings as well. Do the research and give it a try - if you need help with the framework, I can help you.
ForumPosters - good point and it's a question that many beginners ask. However, there are few things that I've found when using cfqueryparam: 1) It tends to cache the last query and not update the new query. By that I mean, let's say you first tell it to pull up all clients with last name 'R' then you tell it to pull up all items with last name 'Q'. I've many occassions where last name 'R' is still being pulled... 2) You know for a fact that the person is not querying your database (live or otherwise). This that point of failure (or access point) is no longer there 3) You have built in 'suggestions' when you use cfsearch. That way you don't ahve to build logic to do it. Verity already did it for you. 4) It's faster - MUCH!
datropics, if you could help me with getting started or "the frame work" that would be great. I have been struggling with this for awhile. My web hosting company doesn't allow the the tag CFCOLLECTION. So really all that I have now is a field in my db called custkeywords that holds words that I search for in my search box. Select * FROM tblCust Where CustKeyWords LIKE '%#form.crud#%' or CustKeyWords LIKE '%#form.crud#' or CustKeyWords LIKE '#form.crud#%' I am not very happy with what I get because it seems very limited and like you said not secure. Please HELP!!!
cfnut, I feel your pain. Ok - so the hosting company doesn't allow CFCollection. Ok, here's what we can do: Use T-SQL to 'act like' what the cfsearch would have done (it's a poor attempt but hec) Consider this website: http://doc.ddart.net/mssql/sql70/setu-sus_8.htm I'm sorry but I'm assuming that it is a MSSQL db you are using. From the site shown above, that should give you a hint as to what you can possibly do. For instance: <cfif IsDefined("Form.submitted")> <cfset FormWords = Trim(Form.srchText)> <cfset WordsArray = ListToArray(FormWords," ")> <cfquery .....> Select ..... From ..... <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> (Difference(WordsArray,CustKeyWords) between 3 and 4) <cfif I neq ArrayLen(WordsArray)> or </cfif> </cfloop> </cfquery> </cfif> However instead of CustKeyWords, you can use the field itself (name, address, profile, whatever). That way you don't have to worry about creating logic to create keywords. Let me know how you get through - sad thing about cfcollection
First of all thanks for helping me detropics!! I added the code that you suggested but I am getting an error. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Difference(pop,CustKeyWords) between 3 and 4) (Difference(pop,CustKey' at line 5 I entered pop in my search text box so it looks like the array picked it up. A couple of notes to your previous post. I am using mysql and CustKeyWords is a field in my DB MY NEW CODE <cfif IsDefined("Form.crud")> <cfset FormWords = Trim(Form.crud)> <cfset WordsArray = ListToArray(FormWords," ")> <cfquery datasource="mydsn" name="seadrchdb"> Select * FROM tblCust <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> (Difference(#WordsArray[I]#,CustKeyWords) between 3 and 4) <cfif I neq #ArrayLen(WordsArray)#> or </cfif> </cfloop> </cfquery> </cfif> Code (markup): Do you know why I am getting this error. I tried adding # and ' to the CustKeyWords but that didn't help. Thanks JN
Yes I do know why, you're using MySQL instead of MSSQL - no worries. The adjusted cfquery should be <cfquery datasource=mydsn" name="seadrchdb"> Select * from tblCust <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> (Soundex(WordsArray) = Soundex(CustKeyWords) ) <cfif I neq #ArrayLen(WordsArray)#> or </cfif> </cfloop>
Hey datropics, one last question/problem. When I type in two words it gives me an error. In my DB Field say I have a list of words(pop chips and other words) and I type more than one word in the search I get this error. But one word works just fine. ERROR MESSAGE Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Soundex(CustKeyWords) LIKE (Soundex('%can%')) or CustKeyWords LIKE '%pop c' at line 9 44 : where Soundex(CustKeyWords) LIKE (Soundex('%#WordsArray[I]#%')) or CustKeyWords LIKE '%#form.crud#%' 45 : <cfif I neq #ArrayLen(WordsArray)#> or </cfif> 46 : </cfloop> 47 : 48 : </cfquery> SQL Select * FROM tblCust where Soundex(CustKeyWords) LIKE (Soundex('%pop%')) or CustKeyWords LIKE '%pop can%' or where Soundex(CustKeyWords) LIKE (Soundex('%can%')) or CustKeyWords LIKE '%pop can%' Code (markup): CODE <cfif IsDefined("Form.crud")> <cfset FormWords = Trim(Form.crud)> <cfset WordsArray = ListToArray(FormWords," ")> <cfquery datasource="Mydsn" " name="seadrchdb"> Select * FROM tblCust <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> where Soundex(CustKeyWords) LIKE (Soundex('%#WordsArray[I]#%')) or CustKeyWords LIKE '%#form.crud#%' <cfif I neq #ArrayLen(WordsArray)#> or </cfif> </cfloop> </cfquery> </cfif> Code (markup):
I see what you were trying to do and there was indeed a syntax error. Note the adjustment below: <cfset FormWords = Trim(Form.crud)> <cfset WordsArray = ListToArray(FormWords," ")> <cfset AllFormWords = ReplaceNOCase(Trim(FormWords)," ","%","ALL")> <cfquery datasource="Mydsn" " name="seadrchdb"> Select * FROM tblCust where (Len(CustKeyWords) >= 1) and ( <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> (Soundex(CustKeyWords) LIKE Soundex('%#WordsArray#%')) <cfif I neq #ArrayLen(WordsArray)#> or </cfif> </cfloop> or (CustKeyWords LIKE '#AllFormWords#') ) </cfquery> daTropics
Well datropics I don't know how to thank you. My search works really well. For those that run into the same problem here is my final code. <cfset FormWords = Trim(Form.crud)> <cfset WordsArray = ListToArray(FormWords," ")> <cfset AllFormWords = ReplaceNOCase(Trim(FormWords)," ","%","ALL")> <cfquery datasource="mydsn" name="seadrchdb"> Select * FROM tblCust where (Length(CustKeyWords) >= 1) and ( <cfloop from="1" to="#ArrayLen(WordsArray)#" index="I"> Soundex(CustKeyWords) LIKE Soundex('%#WordsArray[i]#%') <cfif I neq #ArrayLen(WordsArray)#> or </cfif> </cfloop> or (CustKeyWords LIKE '%#AllFormWords#') or (CustKeyWords LIKE '%#AllFormWords#%') ) </cfquery> Code (markup): If you find yourself using this code thank datropics, HE DA MAN!!! Once again, thanks datropics JN
In the continual pursuit to show the flexibility of ColdFusion even though an easy to learn development language the persons who have studied hard will continue to share - only in sharing can we really ever own and grow in knowledge. Your Welcome - as we say here in Antigua - Nuff Respect!