Published: Thursday, November 30, 2000
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
