Bullschmidt.com - Classic ASP Design Tips - Search For Keywords on Multiple Fields

Bullschmidt.com


Home
Web Database Concept
How We Work Together
Bio
Web Database Sample
Classic ASP Design Tips
Bar Chart Tool
Web Design Resources
Access Database Sample

Classic ASP Design Tips

Search For Keywords on Multiple Fields

These are opinions of J. Paul Schmidt, MBA.

No warranties are either expressed or implied.

To Use Paul's Services: Paul@Bullschmidt.com

o Example of searching for keywords on multiple fields.

For example with Keywords of Go Back Now one gets the SQL Where clause of ((Title Like '%Go Back Now%') OR (Category Like '%Go Back Now%') OR (Descr Like '%Go Back Now%')):

o Example of searching for keywords separately for each word on multiple fields.

For example with Keywords of Go Back Now one gets the SQL Where clause of ((Title Like '%Go%') OR (Category Like '%Go%') OR (Descr Like '%Go%')) OR ((Title Like '%Back%') OR (Category Like '%Back%') OR (Descr Like '%Back%')) OR ((Title Like '%Now%') OR (Category Like '%Now%') OR (Descr Like '%Now%')):

'Keywords = Request.Form("Keywords")
Keywords = "Go Back Now"

' Set var.
KeywordsFixedForSQL = Keywords

' Remove any ".
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, Chr(34), "")

' Double up any single quotes.
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, "'", "''")

If KeywordsFixedForSQL <> "" Then
  ' Set var (array of words).
  strarrayKeywordsFixedForSQL = Split(KeywordsFixedForSQL, " ")

  ' Loop thru items (words).
  For intItemNum = 0 To UBound(strarrayKeywordsFixedForSQL)
    strSQLWhere = strSQLWhere & " OR ("
      strSQLWhere = strSQLWhere & "(Title Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
      strSQLWhere = strSQLWhere & " OR (Category Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
      strSQLWhere = strSQLWhere & " OR (Descr Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
    strSQLWhere = strSQLWhere & ")"
  Next ' Next intItemNum.

  ' Remove initial " OR ".
  strSQLWhere = Right(strSQLWhere, Len(strSQLWhere) - 4)
End If

Response.Write "strSQLWhere: " & strSQLWhere


To Use Paul's Services: Paul@Bullschmidt.com Copyright © 2000-2010 J. Paul Schmidt, MBA Freelance Web and Database Developer All Rights Reserved Privacy Policy