Search form using AND

[Miditen]

Can someone briefly describe the logic I would use so that a single text box can take several keywords separated by AND to query a recordset? Below is the code I am using for a single keyword.

rsHeader.Open ("SELECT DISTINCT InvoiceNo, PartNo" _
& "WHERE CompanyName LIKE '%" & varSearchText & "%' OR " _ 
	& "AcctNo LIKE '%" & varSearchText & "%' OR " _
	& "PartNo LIKE '%" & varSearchText & "%' OR " _
	& "CustomerPO LIKE '%" & varSearchText & "%'") , Conn1

Thanks!

 

[vasah20]

oh so you mean you want seomthing like this:

in the textbox: "black AND decker"

the SQL query: "SELECT * FROM myTbl WHERE field1 LIKE '%black%' AND field1LIKE '%decker%'"

if that's the case, the only way that I've been able to do it is by using regular expressions.
Regular expressions have been available to VBScript since 5.0, non-greedy since 5.5. I would suggest making sure that you have at least VBScript 5.0 installed on your webserver. If you need to install it, it's at

http://www.microsoft.com/scripting

If you're unfamiliar with regexp, this link might help:

http://www.4guysfromrolla.com/webtech/RegularExpressions.shtml

Anyhow, I would do something like this:
- set a regular expression
- get the matches collection
- get the firstindex of the matches
- break apart the string using the matches collection

Dim re, matches, match, andQuery, startPt
startPt = 0
set re = new regexp
re.global=true
re.ignorecase=true
re.pattern = "\band\b"

set matches = re.execute(formQuery)
for each match in matches
andQuery = andQuery & " AND " & mid(formQuery,startPt,match.FirstIndex - startPt)
startPt = match.FirstIndex + 5 'to compensate for the _and_
next

andQuery = Right(andQuery, Len(andQuery) - 5) & mid(formQuery, startPt, Len(formQuery) - startPt)

In theory this should work. I can't test it out because I'm at school right now.

I have done a boolean type search like this before, and actually the code is available at my website. The search allows for OR and AND, and " " type strings. My website is

http://students.depaul.edu/~lmendoza/

it's in downloads, it's the wduw2d.zip... I think it's part of the BBS search, or maybe the searchResults.asp page... I don't really remember.

well - good luck to you.
hth
leo

 

[vasah20]

here's the code I was talking about... i'm just gonna cut and paste it... i'll add a few comments here and there, but you will need to adapt this to your needs....

<%
'creates where clause parsing for AND, OR, and &quot;&quot;
'@strResult - string of the form input
'@field - db field to match against
'returns WHERE condition (without the WHERE)
FUNCTION processStr(strResult, field)
if strResult <> &quot;&quot; then
Response.Write field & &quot;s for : &quot; & strResult & &quot;<br>&quot;
dim re, reMatch, item, boolStr, value, quoteStr, otherStr

boolStr = &quot;&quot;
otherStr = &quot;&quot;
quoteStr = &quot;&quot;
strResult = lcase(strResult)

set re = new RegExp
re.IgnoreCase = true
re.Global = true

'GET THE and &quot;&quot;
re.Pattern = &quot;\sand\s+&quot;&quot;.*?&quot;&quot;&quot;
set reMatch = re.Execute(strResult)
For Each item in reMatch
value = &quot; &quot; & replace(item.value, &quot;&quot;&quot;&quot;,&quot;&quot
value = trim(replace(value, &quot; and &quot;, &quot;&quot)
quoteStr = quoteStr & &quot; AND (&quot; & field & &quot; LIKE '%&quot; & value & &quot;%')&quot;
Next
strResult = re.Replace(strResult, &quot; &quot

'GET THE or &quot;&quot;
re.Pattern = &quot;\sor\s+&quot;&quot;.*?&quot;&quot;&quot;
set reMatch = re.Execute(strResult)
For Each item in reMatch
value = &quot; &quot; & replace(item.value, &quot;&quot;&quot;&quot;,&quot;&quot
value = trim(replace(value, &quot; or &quot;, &quot;&quot)
quoteStr = quoteStr & &quot; OR (&quot; & field & &quot; LIKE '%&quot; & value & &quot;%')&quot;
Next
strResult = re.Replace(strResult, &quot; &quot

'GET REST OF &quot;&quot;
re.Pattern = &quot;&quot;&quot;.*?&quot;&quot;&quot;
set reMatch = re.Execute(strResult)
For Each item in reMatch
value = trim(replace(item.value, &quot;&quot;&quot;&quot;,&quot;&quot)
quoteStr = quoteStr & &quot; AND (&quot; & field & &quot; LIKE '%&quot; & value & &quot;%')&quot;
Next
strResult = re.Replace(strResult, &quot; &quot

re.Pattern = &quot;\sand\s+\w+&quot;
set reMatch = re.Execute(strResult)
For each item in reMatch
boolStr = boolStr & &quot; AND (&quot; & field & &quot; LIKE '%&quot; & trim(Replace(item.value,&quot; and &quot;,&quot;&quot) & &quot;%') &quot;
Next
strResult = re.Replace(strResult,&quot; &quot

re.Pattern = &quot;\sor\s+\w+&quot;
set reMatch = re.Execute(strResult)
For each item in reMatch
boolStr = boolStr & &quot; OR (&quot; & field & &quot; LIKE '%&quot; & trim(Replace(item.value,&quot; or &quot;,&quot;&quot) & &quot;%')&quot;
Next
strResult = re.Replace(strResult,&quot; &quot

re.Pattern = &quot;\s{2,}&quot;
strResult = trim(re.Replace(strResult,&quot; &quot)
set re = nothing

dim i, strArr, arrUB
strArr = Split(strResult,&quot; &quot
arrUB = Ubound(strArr)
For i = 0 to arrUB
otherStr = otherStr & &quot; AND (&quot; & field & &quot; LIKE '%&quot; & strArr(i) & &quot;%') &quot;
Next

'check otherStr, quoteStr, and boolStr
if otherStr = &quot;&quot; then
if quoteStr = &quot;&quot; then
if boolStr = &quot;&quot; then
'RAISE ERROR!
else
processStr = boolStr
end if
else
if boolStr = &quot;&quot; then
processStr = quoteStr
else
processStr = quoteStr & boolStr
end if
end if
else 'otherStr has something
otherStr = Right(otherStr, Len(otherStr) - 4)
if quoteStr = &quot;&quot; then
if boolStr = &quot;&quot; then
processStr = otherStr
else
processStr = otherStr & boolStr
end if
else
if boolStr = &quot;&quot; then
processStr = otherStr & quoteStr
else
processStr = otherStr & quoteStr & boolStr
end if
end if
end if
end if
END FUNCTION


'processes the basic search form
'@QS - string holding form input
'returns sql query
FUNCTION basic(QS)
Response.write &quot;Searched : <br>&quot;
frmQry = &quot;?qry=&quot; & Server.URLEncode(QS)

Dim tempRes
tempRes = processStr(QS, &quot;message&quot
tempRes = correctSQL(correctSQL(tempRes,false), true)
basic = Trim(tempRes)
END FUNCTION

'function strips out the extra ANDs & ORs
'@str - string to format
'@start - boolean to determine where to check
'returns formatted string
FUNCTION correctSQL(str, start)
if start then
if InStr(str,&quot; AND&quot < 4 and InStr(str,&quot; AND&quot <> 0 then
str = Right(str,Len(str) - 4)
elseif InStr(str,&quot; OR&quot < 4 and InStr(str,&quot; OR&quot <> 0 then
str = Right(str,Len(str) - 3)
end if
else
if InStrRev(str,&quot; AND&quot > Len(str) - 4 then
str = Left(str,Len(str) - 3)
elseif InStrRev(str,&quot; OR&quot > Len(str) - 4 then
str = Left(str,Len(str) - 2)
end if
end if
correctSQL = str
END FUNCTION

basic(Request.Form(&quot;search_query&quot)
%>

This hasn't been tested as a cut and paste type setup, I know that it works in my scenario though....

hth you...
leo