To read the article online, visit http://www.4GuysFromRolla.com/webtech/113000-1.3.shtml

Searching 4GuysFromRolla.com, Part 3


  • Read Part 1
  • Read Part 2

  • In Part 2 we began our examination of the /ASPScripts/search.asp script. In this part, we'll complete our dissection of this ASP page!

    Recall that the main task of /ASPScripts/search.asp is to grab the database results and page them. That being said, we need to be able to convert the search terms into a SQL string. This is accomplished with the following code:

      'Now, we've gotta split up the search terms on ORs and ANDs
      Dim colTerms, orTerms
      colTerms = split(txtSearchTerms," and ",-1,1)
    	
      Dim iUpperTerms, iLoop, iUpperOrTerms, iOrLoop
      iUpperTerms = UBound(colTerms)
    	
      Dim strWhereClause
      strWhereClause = ""
    
      For iLoop = LBound(colTerms) to iUpperTerms
        orTerms = split(colTerms(iLoop)," or ",-1,1)
        iUpperOrTerms = UBound(orTerms)
    
        strWhereClause = strWhereClause & "("
        for iOrLoop = LBound(orTerms) to iUpperOrTerms
          strWhereClause = strWhereClause & " Contents LIKE ""%" & _
                           Trim(orTerms(iOrLoop)) & "%"" "
    				
          if iOrLoop < iUpperOrTerms then 
            strWhereClause = strWhereClause & " OR "
          end if      
        next
    
        strWhereClause = strWhereClause & ")"
    
        if iLoop < iUpperTerms then
          strWhereClause = strWhereClause & " AND "
        end if
      Next
    

    This code may seem a bit confusing, but hopefully it will become more clear as we step through it. We start off by breaking up the search terms into an array using the and keyword as a delimiter. (For more information on splitting a string into an array, check out the FAQ: How can I convert a string into an array?) Once we have this array, we grab it's upper bound and store that value in iUpperTerms. We then perform a loop through each element on the array.

    At this point, imagine that we had the search string: ADODB and (OLE-DB or database). Initially, the parenthesis would have been stripped, and txtSearchTerms would have equalled: ADODB and OLE-DB or database. Next, we'd split on the and keyword, so we'd have the array colTerms with two elements: ADODB and OLE-DB or database. Now, as we step through each term of our array colTerms, we split on the keyword or and store the results in the array orTerms. We then step through each element of this array and build up our SQL string, adding a conditional to the WHERE clause of our SQL string. (Recall that the column Contains holds the complete text of the article.)

    For each element in the orTerms array, we place an OR between it in the SQL string and surround the overall OR statement(s) with parenthesis... (Note that putting the or-split as the inner loop and using the parenthesis has the effect of giving the or keyword a higher presedence than the and keyword.) For each term in the colTerms array we place an AND between it. So, assuming that we had our search term of ADODB and (OLE-DB or database), our completed SQL string WHERE clause would be: ( Contents LIKE "%ADODB%" ) AND ( Contents LIKE "%OLE-DB%" OR Contents LIKE "%database%" ).

    Next, we need to open our connection to the database and call our paging stored procedure:

      Dim objConn
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open "DSN=4Guys"
    	
      Dim strSQL
      strSQL = "sp_PagedResults " & iPageNo & "," & _
               RecsPerPage & ",'" & strWhereClause & "'"
    
      Dim objRS
      Set objRS = objConn.Execute(strSQL)
    

    I am not going to step through the sp_PagedResults stored procedure, it is already covered in a previous 4Guys article: Paging through Records using a Stored Procedure. The remainder of the /ASPScripts/search.asp page simply displays the results using HTML and then, as described in the paging stored procedure article, simply packages up all of the variables and stuffs them into a form. When the user clicks on the Next or Prev page buttons, the form is submitted and /ASPScripts/search.asp is revisited with a different page value (but with the same search terms and such reposted back to the script).

    Well, that wraps up this article! If you have any feedback or ideas on how to improve the script, I'd love to hear them! You can download the various scripts we examined in this article in the Attachments section below.

    Happy Programming!


    Attachments:

  • Download the database-building script
  • Download the HTML for /search/index.shtml
  • Download the code for /ASPScripts/search.asp


  • Article Information
    Article Title: Searching 4GuysFromRolla.com, Part 3
    Article Author: Scott Mitchell
    Published Date: Thursday, November 30, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/113000-1.3.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers