How do I Create Logical AND/OR DB search thru script 2

[chdavisjr]

I need some help with a problem ... if you know of an existing thread, here or else where, I would appreciate if you passed it on to me:

I have a large database on our web (

http://agGlossary.uaex.edu)

that I need to change/modify.
The Search page currently allows the user to search the database for a keyword or phrase.

However, the users now want to be able to enter several words, and have the option to search:
1) For the exact string ... no problem here
2) For All of the words (AND)
3) For At least One of the words (OR)
My problem is trying to create an If Then ..ElseIf ... End if
that can test the input.
I thought about having 3 inputs, for example:
Find the Exact string: <input type=text name=findEXACT>
Find Any of the words: <input type=text name=findALL>
Find at Least One Word: <input type=text name=findOR

I know how to use the split() function to break their inputs into individual words to use in SQL, but How Do I Make It Dynamic ... regardless of how long a string they might type in? And then, how do I make the SQL to open the database dynamic, regardless of how many words they type?

Thank you in advance,
Chalmers

 

[PHV]

can you please post the Where clauses expected ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chdavisjr]

My original code looks like this:

sql="SELECT PID, Term, Defn FROM glossary WHERE "
sql=sql+"(Term like '%"+search+"%' OR " 
sql=sql+"Defn like '%"+search+"%') ORDER BY Term ASC;"

If I (currently) pass the searchcriteria as "hay grass", my search looks in Term for this exact string, or, if not found, it looks in the Defn for "hay grass".

Now, they want the 3 options:
1) Search Term for either "hay" or "grass" or search Defn for "hay" or "grass"
2) Search Term for "hay" and "grass" or search Defn for "hay" and "grass"
3) Search Term for "hay grass" or search Defn for "hay grass", as I currently do now.

So here is what I need to do by using loops, or something:
For the example, search= "hay grass" - there could be 1, 2, or 10 words here!:

arrSearch=split("search")
sql="SELECT PID, Term, Defn FROM glossary WHERE "

Select Case Option
 Case "1"
  'For any of the words:
  sql=sql+"((Term like '%"+arrSearch(0)+"%' OR Term like '%"+arrSearch(1)+"%' OR " 
  sql=sql+"Defn like '%"+arrSearch(0)+"%' OR Defn like '%"+arrSearch(1)+"%')) "

 Case "2"
  'For all of the words:
  sql=sql+"((Term like '%"+arrSearch(0)+"%' AND Term like '%"+arrSearch(1)+"%' OR " 
  sql=sql+"Defn like '%"+arrSearch(0)+"%' AND Defn like '%"+arrSearch(1)+"%')) "

 Case else
  sql=sql+"(Term like '%"+search+"%' OR " 
  sql=sql+"Defn like '%"+search+"%') "

End Select

sql=sql+"ORDER BY Term ASC;"

 

[PHV]

And what about something like this ?
arrSearch=Split("search")
For i=0 To UBound(arrSearch)
arrSearch(i)=" '%" & arrSearch(i) & "%' "
Next 'i
sql="SELECT PID, Term, Defn FROM glossary WHERE "
Select Case Option
Case "1" 'For any of the words:
For Each s in arrSearch
sql=sql & "OR Term LIKE" & s & "OR Defn LIKE" & s
Next 's
sql=Mid(sql,4)
Case "2" 'For all of the words:
sql=sql & "(Term LIKE" & arrSearch(0)
For i=1 To UBound(arrSearch)
sql=sql & "AND Term LIKE" & arrSearch(i)
Next 'i
sql=sql & ") OR (Defn LIKE" & arrSearch(0)
For i=1 To UBound(arrSearch)
sql=sql & "AND Defn LIKE" & arrSearch(i)
Next 'i
sql=sql & ") "
Case else
sql=sql & "Term LIKE '%" & search & "%' OR "
sql=sql & "Defn LIKE '%" & search & "%') "
End Select
sql=sql+"ORDER BY Term ASC;"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dilettante]

No matter how you handle your immediate problem, please take time to be aware of the potential you are opening for SQL Injection attacks.

See Protecting Yourself from SQL Injection Attacks for a brief overview and some simplistic suggestions. Follow the links after the article for more info, and be sure to do further research.

 

[chdavisjr]

Thank you, dilettante, for your "heads Up" on this topic, that I was unaware of.

I tried this:
strSearch=replace(searchCriteria,"'","''")

Now, if I enter Farmer's in the Keyword box (searchCriteria)
I am told that Farmer''s does not exist. Before I used the above code, I received an error
Syntax error (missing operator) in query expression '(Term like '%farmer's%' OR Definition like '%farmer's%')'.

I am using a MS Access database. Any suggestions as to why the double apostrophe doesn't work? I thought SQL saw '' as a '?
Chalmers

 

[PHV]

I thought SQL saw '' as a '[:i]
It should.
Are you sure that "Farmer's" exists in your table ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chdavisjr]

Duh!!!! My bad!!
I finally got the "brilliant" idea to check my database, and no wonder the above Replace() didn't work!!

Earlier when I first created this database, I replaced all the secretary's apostrophes with &#39;
So I changed the replace statement on the user's entry to:

strSearch=replace(searchCriteria,"'","&#39;")

and it works!

By the way, thanks to all your work, I was able to get the Search to work:

nsearchCriteria=replace(searchCriteria,"'","'")
searchOption=request("searchOption")

arrSearch=split(nsearchCriteria)	 'Split up the search phrase into individual words

Select Case searchOption

case "1"
 'Option 1 At least 1 of the words OR
 sql="SELECT PID, Term, Definition, SeeAlso FROM glossary WHERE "
 k=0
 x=1
 for iCnt=0 to ubound(arrSearch)-1
  if x=1 then
   sql=sql & "((Term like '%" & arrSearch(iCnt) & "%' OR "
   x=0
  else
   sql=sql & "Term like '%" & arrSearch(iCnt) & "%' OR "
  end if
  k=k+1
 next
 sql=sql & "Term like '%" & arrSearch(k) & "%') OR "

 k=0
 x=1
 for iCnt=0 to ubound(arrSearch)-1
  if x=1 then
   sql=sql & "(Definition like '%" & arrSearch(iCnt) & "%' OR "
   x=0
  else
   sql=sql & "Definition like '%" & arrSearch(iCnt) & "%' OR "
  end if
  k=k+1
 next
 sql=sql & "Definition like '%" & arrSearch(k) & "%')) "
 sql=sql & " ORDER BY Term ASC;"

case "2"
 'Option 2 All of the Words AND
 sql="SELECT PID, Term, Definition, SeeAlso FROM glossary WHERE "
 k=0
 x=1
 for iCnt=0 to ubound(arrSearch)-1
  if x=1 then
   sql=sql & "((Term like '%" & arrSearch(iCnt) & "%' AND "
   x=0
  else
   sql=sql & "Term like '%" & arrSearch(iCnt) & "%' AND "
  end if
  k=k+1
 next
 sql=sql&"Term like '%" & arrSearch(k) & "%') OR "

 k=0
 x=1
 for iCnt=0 to ubound(arrSearch)-1
  if x=1 then
   sql=sql & "(Definition like '%" & arrSearch(iCnt) & "%' AND "
   x=0
  else
   sql=sql & "Definition like '%" & arrSearch(iCnt) & "%' AND "
  end if
  k=k+1
 next
 sql=sql & "Definition like '%" & arrSearch(k) & "%')) "
 sql=sql & " ORDER BY Term ASC;"

case else
 'Use the Exact phrase	
 sql="SELECT PID, Term, Definition, SeeAlso FROM glossary WHERE "
 sql=sql+"(Term like '%"+nsearchCriteria+"%' OR " 
 sql=sql+"Definition like '%"+nsearchCriteria+"%') ORDER BY Term ASC;"
	
end select

Not very elequent, but it works!

 

[PHV]

for iCnt=0 to ubound(arrSearch)-1
Why intentionally don't take in account the last word searched ?
Have you tested your solution with only one word searched ?
Just to know, have you tried mine ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chdavisjr]

Hello, again, PHV.
I have a question about your last suggestion for building the SQL for the "OR" or case 1:

Select Case Option
Case "1"  'For any of the words:
  For Each s in arrSearch
    sql=sql & "OR Term LIKE" & s & "OR Defn LIKE" & s
  Next 's
  sql=Mid(sql,4)

I don't understand what this sql=mid(sql,4) is for; I mean I know what MID() does, but why in your code?
It chops off the SEL in select. I think you were trying to strip out that OR from Where OR, but it doesn't work properly.
I get this error:

sql=ECT PID, Term, Definition, SeeAlso FROM glossary WHERE OR Term LIKE '%bang's%' OR Defn LIKE '%bang's%'OR Term LIKE cattle OR Defn LIKE cattle

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Thanks,
Chalmers

 

[PHV]

Sorry for the typo, should be read like this:
Select Case Option
Case "1" 'For any of the words:
For Each s in arrSearch
whr=whr & "OR Term LIKE" & s & "OR Defn LIKE" & s
Next 's
sql=sql+Mid(whr,4)
The Mid is to get rid of the 1st "OR "

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chdavisjr]

Thanks, PHV
I tried yours, but had a question about the sql=Mid(sql,4)

I took all but the last, so I could end the SQL properly.
I used the k counter for that purpose.

I am still working on your solution, as it is much shorter and seems simplier!
Just haven't gotten the bugs out ... my be my typo, so am checking carefully.
Chalmers

 

[PHV]

Changes from my initial post highlighted:
Select Case Option
Case "1" 'For any of the words:
[highlight]whr=""[/highlight]
For Each s in arrSearch
[highlight]whr[/highlight]=[highlight]whr[/highlight] & "OR Term LIKE" & s & "OR Defn LIKE" & s
Next 's
sql=[highlight]sql+[/highlight]Mid([highlight]whr[/highlight],4)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chdavisjr]

Sorry for my posts AFTER your responses! I was forgetting to close the thread, so wasn't getting your response.
Thanks, fixing to try your change now!

I like your way much better than mine, since it is easier to read!
Chalmers