SQL Search Function Witihin Desc. 1

[rmz8]

How can I make a search function out of SQL that searches within a description. For example, let's say someone described something with the following text:

This is a Chemistry assignment on the composition of oxygen and hydrates.

How can I make it so that an enduser types in queries like:

oxygen composition
composition
oxygen
hydrates
oxygen hydrates

and have that article come up as pertient?

I can't use Verity!

Thanks!


Ryan ;-]

 

[GunJack]

Hey Ryan,

If you're wanting the search to match on a record where one or more of the search words are present in the description field, try something like this.

<cfset sql=&quot;1 = 0&quot;>
<cfloop index=&quot;x&quot; list=#searchWords# delimiters=&quot; &quot;>
<cfset sql=&quot;#sql# or description like '%#replace(x,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#%'&quot;>
</cfloop>

<cfquery ....>
select * from tableName where
#preservesinglequotes(sql)#
</cfquery>

There might be a better way to do it with straight sql but my knowledge of sql is limited to the basics.

GJ

 

[rmz8]

Hey,

What I want to create is a search form with many different options and make a dynamic SQL statement with WHERE clauses that are based on whether the value is entered or not. For example, a user might search by grade level, subject, and the keywords. So, the SQL statement (w/o your code) would be something like:

<cfquery name=&quot;SearchResults&quot;>
SELECT * FROM Resources WHERE <CFIF Form.Subject_Area NEQ &quot;&quot;>Form.Subject = Subject_Area</CFIF>
</cfquery>

I'm just having a problem defining more than one because I don't know how to put AND in the statement. Also, I had a bit of trouble implementing your code.

Ryan ;-]

 

[GunJack]

Hey Ryan,

For that situation, I would use this

where 1=1
<CFIF Form.Subject_Area NEQ &quot;&quot;>
and Subject_Area = Form.Subject
</cfif>
<CFIF Form.Field2 NEQ &quot;&quot;>
and field2 = Form.Field2
</cfif>
.....

Is that what you're looking for?
GJ

 

[rmz8]

Oh, I see, you set 1=1 so that it will always be true and that you can place AND before the other clauses. Yes, that's what I'm looking for. Yet I still don't know how to implement the code in your first post. Let's say that the formfield is called Description and the DSN is LHS.

Ryan ;-]

 

[rmz8]

Nevermind, it works great and I gave you two votes!

Ryan ;-]

 

[GunJack]

Hey Ryan,

The code below should work with the form field set to &quot;description&quot; and the database field named &quot;description&quot; also. What it's doing is treating the form field as a list with the delimiter set to a space. It then loops through this list and creates a dynamic sql statement with each word in the search phrase. The replace function escapes any single quotes so they don't mess up the sql statement. If your database field is not named &quot;description&quot;, you'll need to change the line

<cfset sql=&quot;#sql# or description ....
to
<cfset sql=&quot;#sql# or myFieldName ....

You'll also need to change &quot;article&quot; to the real name of your table. Other than that, I think the code should work as is.

Hope this helps,
GJ

<cfset sql=&quot;1 = 0&quot;>
<cfloop index=&quot;x&quot; list=#Description# delimiters=&quot; &quot;>
<cfset sql=&quot;#sql# or description like '%#replace(x,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#%'&quot;>
</cfloop>

<cfquery name=&quot;SearchResults&quot; datasource=&quot;lhs&quot;>
select * from article where
#preservesinglequotes(sql)#
</cfquery>

 

[GunJack]

Hehe, looks like we posted at the same time. Glad it helped!

GJ

 

[swilliams]

Wouldn't it be better to use <cfindex ...> to index the database fields, and let CF do the matching for you, returning a percentage match, as with all other searches on the web??
Simon

 

[iqof188]

Simon, this is what Ryan wrote in his first post:

I can't use Verity!

So I guess CFINDEX is out of the question...


<webguru>iqof188</webguru>

 

[swilliams]

Didn't spot that!!!
Simon