使用者在搜尋框輸入多個關鍵字 (以空格分隔),需對多個欄位進行模糊匹配,且關鍵字間為 AND 關係 (皆須滿足)。
<%
Function BuildMultiKeywordFilter(keywordStr, fieldArray)
' keywordStr: "關鍵字1 關鍵字2 ..."
' fieldArray: Array("field1", "field2", "field3") 要搜尋的欄位
Dim keywords, kw, i, j, conditions, cond, field, sqlWhere
If Trim(keywordStr) = "" Then
BuildMultiKeywordFilter = ""
Exit Function
End If
keywords = Split(keywordStr, " ")
ReDim conditions(-1) ' 動態陣列
For i = 0 To UBound(keywords)
kw = Trim(keywords(i))
If kw <> "" Then
' 為此關鍵字建立 OR 條件組
ReDim Preserve conditions(UBound(conditions) + 1)
cond = ""
For j = 0 To UBound(fieldArray)
field = fieldArray(j)
If cond <> "" Then cond = cond & " OR "
cond = cond & field & " LIKE '%" & Replace(kw, "'", "''") & "%'"
Next
conditions(UBound(conditions)) = "(" & cond & ")"
End If
Next
If UBound(conditions) >= 0 Then
sqlWhere = " AND (" & Join(conditions, " AND ") & ")"
Else
sqlWhere = ""
End If
BuildMultiKeywordFilter = sqlWhere
End Function
' ===== 使用範例 =====
Dim baseSql, searchFields, whereClause, finalSql
baseSql = "SELECT id, title, content, author, category FROM articles WHERE status = 1"
searchFields = Array("title", "content", "author", "category") ' 要搜尋的欄位
whereClause = BuildMultiKeywordFilter(Request.QueryString("q"), searchFields)
finalSql = baseSql & whereClause & " ORDER BY id DESC"
Response.Write "SQL: " & Server.HTMLEncode(finalSql) & "
"
' 輸出範例:
' SELECT id, title, content, author, category FROM articles WHERE status = 1
' AND ((title LIKE '%asp%' OR content LIKE '%asp%' OR author LIKE '%asp%' OR category LIKE '%asp%')
' AND (title LIKE '%教學%' OR content LIKE '%教學%' OR author LIKE '%教學%' OR category LIKE '%教學%'))
' ORDER BY id DESC
%>
<%
Function BuildParamMultiKeywordFilter(keywordStr, fieldArray, ByRef cmd)
Dim keywords, kw, i, j, conditions, cond, field, paramName, paramIdx
paramIdx = 0
If Trim(keywordStr) = "" Then Exit Function
keywords = Split(keywordStr, " ")
ReDim conditions(-1)
For i = 0 To UBound(keywords)
kw = Trim(keywords(i))
If kw <> "" Then
ReDim Preserve conditions(UBound(conditions) + 1)
cond = ""
For j = 0 To UBound(fieldArray)
field = fieldArray(j)
If cond <> "" Then cond = cond & " OR "
paramName = "@kw" & paramIdx
paramIdx = paramIdx + 1
cond = cond & field & " LIKE " & paramName
cmd.Parameters.Append cmd.CreateParameter(paramName, 200, 1, 255, "%" & kw & "%")
Next
conditions(UBound(conditions)) = "(" & cond & ")"
End If
Next
If UBound(conditions) >= 0 Then
BuildParamMultiKeywordFilter = " AND (" & Join(conditions, " AND ") & ")"
Else
BuildParamMultiKeywordFilter = ""
End If
End Function
' 使用
Dim cmd, sql, whereClause
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
sql = "SELECT * FROM articles WHERE status = 1"
whereClause = BuildParamMultiKeywordFilter(Request("q"), Array("title","content"), cmd)
cmd.CommandText = sql & whereClause & " ORDER BY id DESC"
cmd.CommandType = 1
Set rs = cmd.Execute
%>
動態多關鍵字查詢是搜尋功能的核心,掌握陣列分割、條件組合、參數化三大技巧,即可構建靈活安全的搜尋引擎。