SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: Ranking Multi Field DB Searches
-
Jul 15, 2001, 17:22 #1
- 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%')
Thanks
-
Jul 18, 2001, 10:13 #2
- Join Date
- Apr 2001
- Location
- Des Moines, IA
- Posts
- 346
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
What defines relavance?
-
Jul 18, 2001, 11:21 #3
- 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
-
Jul 27, 2001, 20:34 #4
- 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.
-
Jul 30, 2001, 05:38 #5
- 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