In this article we will discuss how to perform text searching on web site content that is stored in the database. The techniques discussed in this article apply to any database and don't require you to write stored procedures (which some hosting services prohibit you from using.)
Searching by Content Type
One of the most common search types is creating a search for a particular type of content you display on your web site. In this example we will focus on news articles. Basically, you will have a database table to hold your articles such as:
tblArticle (
ArticleID int identity(1,1) not null,
Title varchar(100),
LeadIn varchar(2000),
Body text
Created datetime
)
Lets assume we don't care about the order of the words entered for the search criteria. In this case, we need to split up the search words so that we can build the search query (SQL statement.) You could split up the search words using the Split statement as so:
aWord = Split(Request.Form("Keyword"))
Or you could use the regular expression object if it is available to you:
Dim re, oMatches, oMatch
Set re = New RegExp
re.Global = True
re.Pattern = "[w+-_]+"
Set oMatches = re.Execute(Request.Form("Keyword"))
For Each oMatch In oMatches
Response.Write oMatch.Value
Next
With all of the keywords in hand, you can build the search criteria to find the content from the database. This is done by iterating over each keyword and building the "where clause" part of the SQL statement.
For I = 0 To UBound(aWord)
sWhere = sWhere & " AND (Title LIKE '%" &_
Replace(aWord(I), "'", "''") & "%' " &_
" OR Body LIKE '%" &_
Replace(aWord(I), "'", "''") & "%')"
Next
After we have done this, it is a simple matter of building the query to retrieve the search results from the database:
query = "select * from tblArticle " &_
"where 1=1 " & sWhere
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open oConn, query
Displaying the Search Results
How you display the search results is largely a matter of personal preference. The most popular layout is to show the main title with a short summary underneath. In the case of a search engine, you might also show the URL to the resource below the summary.
With the news article example we were using before, the search results could be built using the following code:
With Response
Do Until oRS.EOF
.Write ""
.Write oRS.Fields("Title").Value
.Write "
" & vbCrLf
.Write oRS.Fields("LeadIn").Value
.Write "
"
oRS.MoveNext
Loop
End With
Ranking Search Results
Optionally, you may choose to rank the search result listings. The simplist way to do this is the count the number of occurances of each keyword in the content. There is no simple way to do this using a simple SQL statement.
The way you can implement this search method is to build a "search indexer" to build a word index for the database conten ahead-of-time. This contains references to every word in the content along with a count of the number of "occurances" for each word.
This way, when a keyword search is performed, you can quickly lookup all of the articles containing the keyword and calculate a "score" based on the number of occurances. The results can be scored and ordered using a simple database query.
