Search Engine with boolean operators (AND and OR)

[danielhai]

I was wondering how I would go about creating a search query that allowed for an AND or OR. I've seen it on many search engines, but I can't get the logic right when searching multiple fields. Is there a way in SQL to dp "where columnname1, columname2 like '%string%' and columnname1, columnname2 like '%string2%'" and it will evaluate the first where clause as true if any have that string?

Just wondering how others did it ... or if you could point me to some good code. Thanks for your help.

 

[lobstah]

This is how I did it:

sSQL = "SELECT * FROM DB_Table WHERE user_id = " & sUser_Id "
If Request.Form(&quot;srch_Customer&quot <> &quot;&quot; Then
sSearch = &quot; AND (Customer LIKE '&quot; & Request.Form(&quot;srch_Customer&quot & &quot;%' &quot;
If Request.Form(&quot;srch_Entered_By&quot <> &quot;&quot; Then
sSearch = sSearch & Request.Form(&quot;srch_Operator&quot & &quot; (first_name LIKE '&quot; & Request.Form(&quot;srch_Entered_By&quot & &quot;%' OR last_name LIKE '&quot; & Request.Form(&quot;srch_Entered_By&quot & &quot;%')) &quot;
Else
sSearch = sSearch & &quot &quot;
End If
sSQL = sSQL & sSearch
Else
If Request.Form(&quot;srch_Entered_By&quot <> &quot;&quot; Then
sSearch = &quot; AND (first_name LIKE '&quot; & Request.Form(&quot;srch_Entered_By&quot & &quot;%' OR last_name LIKE '&quot; & Request.Form(&quot;srch_Entered_By&quot & &quot;%') &quot;
sSQL = sSQL & sSearch
End If
End If

srch_Entered_By and srch_Customer are my two search criteria fields from the search form. srch_Operator is the AND/OR selection, so it's value is either AND or OR. And I surround the comparisons in parens so that it keeps them intact in case of an OR situation.

does that help?