Multiple Search Parameters

[Panlflzs]

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.

<form method="post" action="../dept_search.cfm"><center>
<input name="search" type="text" />
<br />
<input type="submit" value="Department Search" />
</form>

And here is the query statement:

<cfquery name="search" datasource="dbname">
			SELECT * 
			FROM dbtable
			HAVING name LIKE '%#form.search#%'
			OR description LIKE '%#form.search#%' OR keywords LIKE '%#form.search#%'
		</cfquery>

Any ideas?

[acoder]

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)

[Panlflzs]

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.

[Panlflzs]

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:

<CFQUERY NAME="search" DATASOURCE="db">
    SELECT *
    FROM dbtable
    WHERE(
    <cfloop index="i" list="#form.search#" delimiters=" ">
        <cfoutput>name LIKE '%#i#%' OR keywords LIKE '%#i#%' OR</cfoutput>
    </cfloop>
    '%%')

</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?

[acoder]

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.

[Panlflzs]

Could you possibly give me a code example of what you mean. Thank you.

[acoder]

Make a list of common words:

<cfset commonwords = "the,this,is,a,...">

Define a function that will search through the list and produce a new list, e.g.

<cfscript>
function removeCommon(lis) {
	lis2 = "";
	for (i=1; i lte listLen(lis); i=i+1) {
		if(not listFindNoCase(commonwords,listGetAt(lis,i))) lis2 = lis2 & listGetAt(lis,i) & ",";
	}
	return lis2;
}
</cfscript>

[Panlflzs]

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.

[acoder]

What's the error message and on what line?

[Panlflzs]

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.

[Panlflzs]

I guess to be more clear I should say:

      <cfscript>
      function removeCommon(lis) {
          lis2 = "";
          for (i=1; i lte listLen(lis); i=i+1) {
              if(not listFindNoCase(commonwords,listGetAt(lis,i))) lis2 = lis2 & listGetAt(lis,i) & ",";
          }
          return lis2;
      }
      </cfscript>

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.

[acoder]

No, that is just a function called a UDF (user-defined function). You can use it like you would a built-in function.

Try:
[CODE=cfm]<cfset newlist = removeCommon(fo rm.search)>[/CODE] Now newlist would contain the list of search items with the common words removed.