SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ranking Multi Field DB Searches

    I am searching through multiple fields in my database and I need to return my results in descending relevance. How can I do this using ASP +/ SQL? A shortened form of my current statement would be like this:

    Code:
    SELECT * FROM TableName WHERE (Experience LIKE '%web%') OR (Experience LIKE '%design%') OR (Qualifications LIKE '%web%') OR (Qualifications LIKE '%design%')
    I need to order my recordset starting with the record containing the most matches then down.

    Thanks

  2. #2
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What defines relavance?

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    The best way would be to create a temp table and build counts in there, then return all the rows based on the count in the temp table. Here is a set of code which I think will do what you want....

    Code:
    dim keywords, SearchString, SearchWord
    
    ' Create the table.....
    strSQL = "CREATE TABLE TempTable (MemberID, TimesFound)"
    my_Conn.Execute strSQL
    
    SearchString = ""
    keywords = split(Request.Form("Search"), " ")
    For Each word in keywords
    	if instr(1, SearchString, word) > 0 then
    		' Do Nothing - Word already searched for....
    	else
    		SearchWord = Replace(word, "'", "''")
    		SearchWord = HTMLEncode(SearchWord)
    		SearchString = SearchString & SearchWord
    
    		SelectMembers(1)		' Search for qualifications first....
    		SelectMembers(2)		' Search for experience next....
    	end if
    next
    
    ' Select the records based on the temp table
    StrSql = "SELECT * From TableName WHERE MemberID = " & _
             " (SELECT MemberID from TempTable ORDER BY TimesFound)"
    rs.Open strSql, my_Conn, 3,1
    if rs.EOF or rs.BOF then
    	' Do Nothing - no records found
    else
    	do until rs.EOF
    			' Do your display code here....
    		rs.MoveNext
    	loop
    end if
    set rs = nothing 
    rs.close
    
    ' Drop the table when done...
    strSQL = "DROP TABLE TempTable"
    my_Conn.Execute strSQL
    
    Function SelectMembers(type)
    	StrSQL = "SELECT Distinct MemberID FROM TableName WHERE "
    	If Type = 1 then
    		StrSQL = StrSQL & " Experience "
    	Else
    		StrSQL = StrSQL & " Qualifications "
    	End if
    	rs.Open strSql, my_Conn, 3,1
    	if rs.EOF or rs.BOF then
    		' Do Nothing - no records found
    	else
    		do until rs.EOF
    			MemberID = rs("MemberID")
    			StrSQL = "SELECT Count(*) as NumCount from TempTable where MemberID = " & MemberID
    			set rsCount = my_Conn.Execute (strSql)
    			if rsCount.BOF or rsCount.EOF then
    				' No Subscriptions found, do nothing
    			else
    				If rsCount("NumCount") = 0 then
    					strSQL = "INSERT INTO TempTable (MemberID, TimesFound) " & _
    					         " VALUES (" & MemberID & ", 0)
    				else
    					strSql = "UPDATE TempTable SET TimesFound = TimesFound + 1 " & _
    					         " WHERE MemberID = " & MemberID
    				end if
    				my_Conn.Execute(strSql)
    			end if
    			rsCount.Close
    			set rsCount = nothing
    			rs.MoveNext
    		loop
    	end if
    	set rs = nothing 
    	rs.close
    End Function
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dave, I just got that code adapted to my needs today. I also learned some things I didn't know in those SQL statements.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Glad I could help!!!!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •