Published: Tuesday, January 09, 2001
Extending the Customizable Banner Rotation System
By Peter McMahon
| Part 2 of a Two-Part Article |
|---|
|
This article, Extending the Customizable Banner Rotation System, is the second part of a
two-part series. The first part, Developing a Customizable
Banner Rotation System, discusses important topics leading up to this article. Be sure
to have read the first part before tackling this article. Additionally, for more information
on Banner Rotation Systems, refer to the Banner Rotation
System Article Index.
|
Banner Categories in a Search
When you enter a search in Yahoo!, the banner in the results page pertains to the search
criteria that you entered. This is another way of convincing advertisers that their banner
will be viewed almost exclusively by their target audience, and thus they are getting better
value for money. You too can achieve this by running two searches internally each time a
person runs a search on the site. However, you'd need to add another column to the
Categories table first – to store a list of keywords – which would increase your
chances of a match (you could run the search against the name of the category). Your
Categories table would now look like this:
Categories Table |
|---|
| Field | Type [Access] | Type [SQL Server] |
|---|
CategoryID | Autonumber | Integer [Identity] |
Name | Text [255] | varchar [255] |
Keywords | Memo | varchar [1000] |
You would now add appropriate keywords for each category:
| CategoryID | Name | Keywords |
|---|
| 1 | Computers | computers internet software hardware |
| 2 | Sport | sport golf soccer olympics athletics |
| 3 | Media | media news magazines music mp3s |
Naturally you would add a lot more keywords than I have. Now to the code. I'm only going to
build a quick-and-easy engine as the topic of this article is a Customisable Banner Rotator,
not a customisable search engine. I'll assume that you have a table called SearchEntries
in the same database as we've added the Banners and Categories tables
to, that contains the list of links on your search engine. Here's a basic idea of the table
layout for it:
SearchEntries Table |
|---|
| Field | Type [Access] | Type [SQL Server] |
|---|
EntryID | Autonumber | Integer [Identity] |
Name Text [255] | varchar [255] |
URL Text [255] | varchar [255] |
Description Text [255] | varchar [255] |
Keywords Memo | varchar [1000] |
On our form that references to our search.asp file, we'll assume that it has a
text inputbox named txtSearch and we use it to query our SearchEntries
table using a SELECT SQL query on the Keywords column. That doesn't change. However,
you need to get a CategoryID to select a banner with. This you do by running a
SELECT SQL query on the Keywords column of the Categories table
using the search text. You then use this value when you randomly grab a banner from the
Banners table. Here's what our code would look like:
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
Dim objConn, objRS, intCat, arrBanners, intMax, intRand, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=BannerRotator;"
Set objRS = objConn.Execute("SELECT CategoryID FROM Categories " & _
WHERE Keywords LIKE '%" & Request.Form("txtSearch") & "%'")
If objRS.EOF Then
intCat = 0
Else
IntCat = objRS("CategoryID")
End If
objRS.Close
strSQL = "SELECT ImageSrc, LinkURL, ALT FROM Banners"
If intCat <> 0 Then
strSQL = strSQL & " WHERE CategoryID = " & intCat
End If
objRS.Open strSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText
intMax = objRS.RecordCount
arrBanners = objRS.GetRows()
objRS.Close
Randomize
intRand = Round(Rnd(intMax) * (intMax - 1))
%>
<A HREF="<%=arrBanners(1,intRand)%>" TARGET="_top">
<IMG SRC="banners/<%=arrBanners(0,intRand)%>" WIDTH="468"
HEIGHT="60" BORDER="0" ALT="<%=arrBanners(2,intRand)%>">
</A><BR><BR>
<B>Search Results</B><BR><BR>
<%
objRS.Open "SELECT Name, URL, Description FROM SearchEntries " & _
"WHERE Keywords LIKE '%" & Request.Form("txtSearch") & "%'", _
objConn, adOpenKeyset, adLockOptimistic, adCmdText
While Not objRS.EOF
Response.Write "<A HREF=" & objRS("URL") & ">" & _
objRS("Name") & "</A><BR>" & objRS("Description") & _
"<BR><BR>"
Wend
objRS.Close 'Clean up...
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
|
Again I stress that this is by no means a definitive reference on how to build a search
engine, so please don't treat it as such. However the method of determining which category
of banner to display should prove sufficient for the needs of most websites. (For more
information on building a search engine for your site, check out: Searching
4GuysFromRolla.com and Searching Through the Text of Each
File on a Web Site!)
In all of our discussion of this banner roation system, we have failed to mention how to record
statistics for the number of times a banner has been displayed and the number of times a
banner has been clicked. In Part 2 we'll look at
how to accomplish this!
Read Part 2!
