Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Parallel level searching..

Status
Not open for further replies.

webmigit

Programmer
Joined
Aug 3, 2001
Messages
2,027
Location
US
Hello folks..

I run a site called bibleclicks.com which allows a powerful reference and study system through a bible search engine..

I'm able to enter Jesus & Christ.. and it will show me every single verse that contains "Jesus" AND "Christ"

Now tonight.. I wanted to create the ability to search (Jesus / Christ) & (Moses / God) or such similar things.. and guess what? didn't work.. the outputted code is correct.. I know because when I place the outputted string as the static WHERE statement.. all works pretty well..

Would someone mind helping me turn this code around into the proper code? (Searctext=the search text entered by the user...)

If this code is just so far off what needs to be written let me explain that all I'm trying to do is replace "{" with "(" and "}" with ")".. but the parentheses need to act as search term groupings in the actual sql...

<CFSET CompileSR=&quot;#Replace(Replace(CompileSR,&quot;'%{&quot;,&quot;('%&quot;,&quot;ALL&quot;),&quot;}%'&quot;,&quot;%')&quot;,&quot;ALL&quot;)#&quot;>
<CFIF left(searctext,1) is &quot;{&quot;>
<CFSET CompileSR=&quot;#Right(CompileSR,len(compileSR)-1)#&quot;>
<CFSET CompileSR=&quot;(#CompileSR#&quot;>
</CFIF>
<CFSET compileSR=&quot;%#ReReplaceNoCase(ReReplaceNoCase(ReReplaceNoCase(searctext,&quot;Qnot&quot;,&quot;%' And NOT Textdata like '%&quot;,&quot;ALL&quot;),&quot;Qor&quot;,&quot;%' OR Textdata like '%&quot;,&quot;ALL&quot;),&quot;Qand&quot;,&quot;%' AND Textdata like '%&quot;,&quot;ALL&quot;)#%&quot;>
<CFSET CompileSR=&quot;#Replace(CompileSR,&quot;%%&quot;,&quot;%&quot;,&quot;ALL&quot;)#&quot;>
<CFSET CompileSR=&quot;#Replace(CompileSR,&quot; %&quot;,&quot;%&quot;,&quot;ALL&quot;)#&quot;>
<CFSET CompileSR=&quot;#Replace(CompileSR,&quot;% &quot;,&quot;%&quot;,&quot;ALL&quot;)#&quot;>

And the query...

<CFLOCK scope=&quot;Session&quot; timeout=&quot;30&quot; type=&quot;Exclusive&quot;>
<CFQUERY name=&quot;caselist&quot; datasource=&quot;thebible&quot;>
SELECT BookTitle, book, chapter, verse, textdata From #table#
WHERE 0=0
<CFIF searctext NEQ &quot;&quot;>
AND (textData like '%#CompileSR#%')
</CFIF>
<CFIF bookTitle NEQ &quot;&quot;>
AND (Book='#Replace(bookTitle,&quot;,&quot;,&quot;' OR Book='&quot;,&quot;ALL&quot;)#')
</CFIF>
<CFIF chapter NEQ &quot;&quot;>
AND Chapter='#chapter2#'
</CFIF>
<CFIF verse NEQ &quot;&quot;>
AND verse='#verse2#'
</CFIF>
ORDER BY book ASC, chapter ASC, verse ASC
</CFQUERY>
</CFLOCK>
 
Just to clarify is the static where statement

WHERE
(
textData LIKE '%Jesus%'
OR
textData LIKE '%Christ%'
)
AND
(
textData LIKE '%Moses%'
OR
textData LIKE '%God%'
)

So you want J-M AND J-G AND C-M AND C-G combinations

What sort of input field are you using?
A single text box?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top