|
|
I did end up using CONTAINS, but it took me a few tries to get it right.
Contains with an 'OR' is easy, but contains with 'AND' means that each word in
your search criteria needs to be in a different function. And none of those
functions can fail, or the whole thing will fail.
So first I had to clean up the search critiera, then do the search itself. I
have attached code samples below.
Thanks for the suggestions-
<!---clean up the search term so it works with the full-text--->
<cfset
badlist="1,2,3,4,5,6,7,8,9,0,$,!,@,$,%,^,&,*,(,),-,_,+,=,[,],{,},about,after,
all,also,an,and,another,any,are,as,at,be,because,been,before,being,between,both,
but,by,came,can,come,could,did,do,does,each,else,for,from,get,gets,got,has,had,h
e,have,her,here,him,
himself,his,how,if,in,into,is,it,its,just,like,make,many,me,might,more,most,much
,must,my,never,now,of,on,only,or,other,our,out,over,re,said,same,
see,should,since,so,some,still,such,take,than,that,the,their,them,then,there,the
se,they,this,those,through,to,too,under,up,use,very,want,was,way,we,well,were,wh
at,when,where,which,while,
who,will,with,would,you,your,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
">
<cfparam name="fullsearch" default="">
<!---separate words from the search--->
<cfif len(trim(search)) gt 2>
<cfset cleansearch=replace(trim(search), ' ', ',', 'all')>
<cfloop list="#cleansearch#" index="nextterm">
<!---if this term is in the list of bad words, then don't add
it to
the list--->
<cfif not listcontains(badlist, nextterm)>
<cfset fullsearch='FORMSOF(INFLECTIONAL, #NEXTTERM#)
and
#fullsearch#'>
</cfif>
</cfloop>
</cfif>
<!---shorten this to take out the last 'and', but don't shorten if it is blank
already--->
<cfif len(trim(fullsearch)) gt 4>
<cfset searchstring=left(fullsearch, len(trim(fullsearch))-4)>
<cfelse>
<cfset searchstring="NoSearchStringSpecified">
</cfif>
Now run the query with the searchstring- a lot of this query is unimportant
for this demo, but I didn't want to take out parts that would break it.
<!---this is the query for a search--->
<cfquery datasource="anr" name="popart">
select sum(a.hittotal) as masshits, b.articlenum, b.title, b.fileext,
b.shortabstract, b.artpass, b.groupid, b.status
from reparticles b left outer join rephits a
on a.articlenum=b.articlenum
WHERE CONTAINS(b.*,'#searchstring#')
<cfinclude template="imagesearch.cfm">
group by b.articlenum, b.title, b.fileext, b.shortabstract, b.artpass,
b.groupid, b.status
order by
<!---this puts the peer reviewed items on top--->
CASE
WHEN status = 20 THEN 1
ELSE 0
END desc,
masshits desc
</cfquery>
|
|