Search Problem for "Match Atleast One of the Words"

[maverik59]

Hi I'm stuck. I have written some ASP to search my database but i want the user to be able to "match atleast one of the words in the database" I've put the search string into an array but have no idea how to go about putting the array into the SQL. Can anyone help?

Heres the code where i select.
If SC ="morethan1" THEN

Dim sentencewords
aWords = split(SearchText, " ")

'Open our recordset
SQL = "SELECT * from advert WHERE (((table.title) = '%" & awords & "%')) OR (((table.bf) = '%" & SearchText & "%')) OR (((table.type) = '%" & awords & "%'));"
rs.Open SQL, objConn

End if

Many thanks if anyone can help

 

[DreXor]

you'll have to do a loop cycle on it, like if you have say..


catagory, mfg, and description you want to search in

and search for hi-fi headphones

you'll need to :

fields = split("catagory,mfg,description",",")
awords = split("hi-fi headphones"," ")

for each field in fields
for each word in awords
Sqlconditions = sqlconditions & field & " LIKE '%" & word & "%' OR "
next
next

' then you'll need to strip the last "OR" off
sqlconditions = left(sqlconditions,len(sqlconditions)-3)) ' -3 for O, R, and the last space

SQLstmt = "Select * from table where (" & SQLconditions & ")" ' add AND whatever other required items after

that should help you a little

 

[DreXor]

oh, and to add on, just incase of many words, and many fields, standard variables have a string length limit, may want to dim sqlstmt(0) and then assign everything into it, to avoid truncation

 

[Tarwn]

Quick correction: I believe you meant to use the LIKE operator in your statement rather than equal signs (at least from the presence of the %'s I assume that)

Another option would be to use the Replace function to replace the spaces with the necessary SQL and then append the front and rear stuff on top of that:

Function BuildKeywordSQL(searchWords,fieldName)
   'prefix - start the first check
   BuildKeywordSQL = "(" & fieldName & " LIKE '%"
   'replace spaces
   BuildKeywordSQL = BuildKeywordSQL & Replace(searchWords," ","%' OR " & fieldName & " LIKE '%"
   'suffix - end the last check
   BuildKeywordSQL = BuildKeywordSQL & "%')"
End Function

'then to use the function just call it with the name of each field you want to check
'   I added line continuations to make it more readable
SQL = "SELECT * from advert WHERE " & _
   BuildKeywordSQL(SearchText,"table.title") & " OR " & _
   BuildKeywordSQL(SearchText,"table.bf") & " OR " & _
   BuildKeywordSQL(SearchText,"table.type") & ";"

The only problem that would have is that if someone put multiple spaces between the words then it would return everything in the database

A workaround for that would be to use a regular expression eplace on "\s+" instead of the std replace. That way it would replace all occurrences of one-or-more spacs in a row with the SQL chewy center.

Anywho, just thought I would post it, i think in this case you should go with Drexors solution but mine might beinteresting to people also

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[DreXor]

 

[maverik59]

Thanks alot guys, I'm working on it now.