By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Multiple Search Parameters

P: 9
Howdy,
I am working on a system where I need a basic HTML form to pass data to a cfm page. The cfm page will then query a database and return any matching data. I am using MySQL.

I have my HTML page with a form element that allows a user to enter search criteria. When they click submit it sends the data in the text field to a coldfusion page that runs that data through a query to the database. If a match is found it should display the resulting data. The form works, it sends the complete request to the coldfusion page with no problem. The only problem is that when you enter more than one word, even if both of those words are in the database, it doesn't return any records. I'm wondering if anyone has any ideas on how to fix this, I was thinking of somehow breaking the input string into parts and running each individual part by itself, I'm just not sure how to do that.

Here is the HTML form code.
Expand|Select|Wrap|Line Numbers
  1. <form method="post" action="../dept_search.cfm"><center>
  2. <input name="search" type="text" />
  3. <br />
  4. <input type="submit" value="Department Search" />
  5. </form>
And here is the query statement:
Expand|Select|Wrap|Line Numbers
  1. <cfquery name="search" datasource="dbname">
  2.             SELECT * 
  3.             FROM dbtable
  4.             HAVING name LIKE '%#form.search#%'
  5.             OR description LIKE '%#form.search#%' OR keywords LIKE '%#form.search#%'
  6.         </cfquery>
Any ideas?
May 31 '07 #1
Share this Question
Share on Google+
11 Replies


acoder
Expert Mod 15k+
P: 16,027
Welcome to TSDN!

So you want to split the string and search for string1 or string2, i.e. if string1 or string2 appears in a record, it should be returned in the search? (not string1 AND string2)
Jun 1 '07 #2

P: 9
Welcome to TSDN!

So you want to split the string and search for string1 or string2, i.e. if string1 or string2 appears in a record, it should be returned in the search? (not string1 AND string2)
I actually would like it to search for string1 AND string2. I'm just not sure of the best way to split it up and have each part run through a query. The splitting of the string is the biggest problem I am having.
Jun 1 '07 #3

P: 9
I actually would like it to search for string1 AND string2. I'm just not sure of the best way to split it up and have each part run through a query. The splitting of the string is the biggest problem I am having.
I actually found something out that helped me. I changed my query to this:
Expand|Select|Wrap|Line Numbers
  1. <CFQUERY NAME="search" DATASOURCE="db">
  2.     SELECT *
  3.     FROM dbtable
  4.     WHERE(
  5.     <cfloop index="i" list="#form.search#" delimiters=" ">
  6.         <cfoutput>name LIKE '%#i#%' OR keywords LIKE '%#i#%' OR</cfoutput>
  7.     </cfloop>
  8.     '%%')
  9.  
  10. </CFQUERY>
This is working like a charm, the only thing is if I type something like "This is a wonderful day" the search is finding every term. For instance I only want it to find wonderful and day, but since I type the whole sentance it is also searching for is, a, and this. These are common words that I don't want it to find. Do you know anyway to have these not be searched for?
Jun 1 '07 #4

acoder
Expert Mod 15k+
P: 16,027
This is working like a charm, the only thing is if I type something like "This is a wonderful day" the search is finding every term. For instance I only want it to find wonderful and day, but since I type the whole sentance it is also searching for is, a, and this. These are common words that I don't want it to find. Do you know anyway to have these not be searched for?
Have a list of common words and if a particular search term matches then discard it. In your loop you could do a ListFind for each search term. If it doesn't match, then add the query string.
Jun 1 '07 #5

P: 9
Could you possibly give me a code example of what you mean. Thank you.
Jun 4 '07 #6

acoder
Expert Mod 15k+
P: 16,027
Make a list of common words:
Expand|Select|Wrap|Line Numbers
  1. <cfset commonwords = "the,this,is,a,...">
Define a function that will search through the list and produce a new list, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfscript>
  2. function removeCommon(lis) {
  3.     lis2 = "";
  4.     for (i=1; i lte listLen(lis); i=i+1) {
  5.         if(not listFindNoCase(commonwords,listGetAt(lis,i))) lis2 = lis2 & listGetAt(lis,i) & ",";
  6.     }
  7.     return lis2;
  8. }
  9. </cfscript>
  10.  
Jun 5 '07 #7

P: 9
I apologize for my ignorance, but I can't seem to get your code to work. Coldfusion just returns an error. I am copying directly from your code, of course removing the numbers, and it still doesn't work. I'm not sure what variables I am supposed to set. Thanks for any help.
Jun 18 '07 #8

acoder
Expert Mod 15k+
P: 16,027
What's the error message and on what line?
Jun 19 '07 #9

P: 9
I couldn't tell you. The way the coldfusion that I am using is set up a default error page will come up to tell you something in coldfusion is broken. Just imagine how hard it is to develop in this environment. I can tell you that I getting the input from a form on a page then doing all my work on the next page. The list of words that I need to check would be form.search, or in coldfusion #form.search#. I just don't know where or how to add that piece to the code you've given me.
Jun 19 '07 #10

P: 9
I guess to be more clear I should say:

Expand|Select|Wrap|Line Numbers
  1.       <cfscript>
  2.       function removeCommon(lis) {
  3.           lis2 = "";
  4.           for (i=1; i lte listLen(lis); i=i+1) {
  5.               if(not listFindNoCase(commonwords,listGetAt(lis,i))) lis2 = lis2 & listGetAt(lis,i) & ",";
  6.           }
  7.           return lis2;
  8.       }
  9.       </cfscript>
  10.  
Where do I put #form.search# which is my original string of words. Should I type <cfset #form.search# = lis> befor this code? Something like that? I hope this a little more clear. I really do appreciate all your help.
Jun 19 '07 #11

acoder
Expert Mod 15k+
P: 16,027
No, that is just a function called a UDF (user-defined function). You can use it like you would a built-in function.

Try:
Expand|Select|Wrap|Line Numbers
  1. <cfset newlist = removeCommon(form.search)>
Now newlist would contain the list of search items with the common words removed.
Jun 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.