Multiple word, Multiple field table search

Results 1 to 2 of 2

Thread: Multiple word, Multiple field table search

  1. #1
    Shawn Clabough Guest

    Default Multiple word, Multiple field table search

    Does anyone have some sample code that will take a keyword variable that contains multiple words and search a table for the records that contain those words in any given fields?<BR><BR>For example: A table contains two fields - Title, Description and user enters &#039word1 word2&#039 in the search field. <BR><BR>The returned results would include any record that contains either:<BR> word1 and word2 in the title<BR> word1 and word2 in the description<BR> word1 in the title and word2 in the description<BR> word1 in the description and word2 in the title<BR><BR>This should be scalable up to any number of fields.<BR><BR>TIA, Shawn.<BR>

  2. #2
    Shawn Clabough Guest

    Default RE: Multiple word, Multiple field table search

    Well, I think perhaps a night&#039s sleep helped me clear my mind and figure out a way to do what I want. It could also be done nicely recursively, but I took the easy way out. This code will parse an input field for search words and return a recordset with each record that has the search words in any of the given fields. Here&#039s what I did:<BR><BR>strSQ = chr(39)<BR>strSearchWords = Replace(Request.form("searchwords"), "&#039", "&#039&#039")<BR><BR>If IsObject(Session("DB_conn")) Then<BR> Set conn = Session("DB_conn")<BR>Else<BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> conn.open "DB","",""<BR> Set Session("DB_conn") = conn<BR>End If<BR><BR>&#039 Search fields Title, Overview and Body from table Articles<BR><BR> if strSearchWords &#062; "" then<BR> entry_length = len(strSearchWords)<BR> searchwords = strSearchWords & " "<BR> position = 1<BR> subQuery = "Articles"<BR> do<BR> start_word = position<BR> end_word = InStr(position, searchwords, " ")<BR> word = mid(searchwords, start_word, (end_word - start_word))<BR> position = end_word + 1<BR><BR> sql = "SELECT * FROM (" & subQuery & ") WHERE ("<BR><BR> sql = sql & "(Title LIKE "& strSQ & "%" & word & "%" & strSQ & ") OR "<BR> sql = sql & "(Overview LIKE "& strSQ & "%" & word & "%" & strSQ & ") OR "<BR> sql = sql & "(Body LIKE "& strSQ & "%" & word & "%" & strSQ & ")) "<BR><BR> subQuery = sql<BR><BR> loop until position &#062;= entry_length<BR> end if<BR><BR> Set final_rs = Server.CreateObject("ADODB.Recordset")<BR> final_rs.Open sql, conn, 3, 3<BR>

Posting Permissions

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