I have a web page that searches 2 fields of a database, this works fine if all the words serched are in one field but does not work if the words are in seperate fields.
It searched names of mp3 files and also a field that conatins various words to describe it, so if you type in female west country into the search field and none of the filenames contain the word female it returns no results unless the three words are in the description field, i am sure this can be resolves in the sql statement which is generated by asp.
here is the code
- <%
-
-
if InStr(session("recordsInCart"), ","&request.form("recordNum")) = 0 then
-
session("recordsInCart") = session("recordsInCart") + request.form("recordNum") &","
-
else
-
'do nothing
-
end if
-
%>
-
<%
-
Dim strSearch, myarray, strSQL, strSQLExtra
-
If Not Request.Form("search") = "" Then
-
session("search") = Request.Form("search")
-
session("choice") = Request.Form("choice")
-
session("queryterm") = session("queryterm")+" "+Request.Form("search")
-
End If
-
-
'Loop is now in a function, so that we can feed it each field name.
-
Function addparams(fieldname,wordArray)
-
Dim tmp
-
tmp = ""
-
For each item in wordArray
-
tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & session("choice") & " "
-
next
-
tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND "
-
addparams = tmp
-
End Function
-
-
'Build the word array
-
myarray = split(session("search"), " ")
-
-
'Build the SQL
-
strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE ("
-
-
strSQL = strSQL & addparams("filename", myarray) ' change field one!
-
-
strSQL = strSQL & ") OR ("
-
-
strSQL = strSQL & addparams("mp3type", myarray) ' change field two!
-
-
strSQL = strSQL & ")order by surname, firstname, filename;"
-
session("sqlset") = strSQL
-
-
%>
i hope someone can help
