SQL Parallel level searching..

[webmigit]

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 &quot".. 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>

 

[cfk]

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?