ASP Database Column search

[tripm8]

Hi I am looking for a asp script, that will do an OR search on keywords in two differant columns in an MySQL database.

for example if someone searches for joe bloggs I want to bring back all records that have either of the words joe or bloggs in column one or column two.

I have found a script that does exactly what I want but it will only search one column of a database whereas i would want it to search two columns.

http://www.4guysfromrolla.com/webtech/052799-1.shtml

Can anyone give me pointers on how to modify this code or point me in the direction of some other code that will do the same job.

Many thanks

 

[steven290]

try your sql like this

select from tblname 
where col1 like '%joe%' 
or col1 like '%bloggs%' 
or col2 like '%joe%' 
or col2 like '%bloggs%'

 

[tripm8]

thanks that works..but it depends what a user types in a search box.....so it is a variable number of words and variable words

 

[vongrunt]

This is simplified example (missing support for %_'", double words can be removed etc) but overall idea is clear IMO:

Function getSearchSQL( sBase, aColumns, sSearch, bAllWords )
	Dim sBool: If bAllWords Then sBool = " AND " Else sBool = " OR "
	Dim aTerms:	aTerms = Split(sSearch, " ")
	Dim aTmp: aTmp = Array(): ReDim Preserve aTmp(UBound(aColumns))
	Dim sToken, i
	Dim sRet: sRet = sBase
	Dim sWhere: sWhere = ""
	
	For Each sToken in aTerms
		If Trim(sToken) <> "" Then
			'modify sToken here if necessary
		
			For i = 0 To UBound(aColumns)
				aTmp(i) = aColumns(i) & " LIKE '%" & sToken & "%'"
			Next
			
			If sWhere <> "" Then sWhere = sWhere & sBool
			sWhere = sWhere & "("& Join( aTmp, " OR " ) & ")"
		End If
	Next
	
	If sWhere <> "" Then sRet = sRet & " WHERE " & sWhere

	getSearchSQL = sRet
End Function
%>

Sample call:

Dim sSQL
sSQL = getSearchSQL( "select somecolumns from sometable", _
	Array( "col1", "col2" ), _
	"john bloggs ", _
	true )
		
response.write "<xmp>" & sSQL & "</xmp>"

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba