Published: Wednesday, January 03, 2001
Developing a Customizable Banner Rotation System, Part 2
By Peter McMahon
Read Part 1
In Part 1 we examined one (of many)
ways to create a banner rotation system: by using Microsoft's free
Ad Rotator component. In this part we'll examine using a more
customizable approach: a custom, data-driven app.
The Database Solution
There are two primary advantages of using a database to store the
banner definitions. Firstly, a database is flexible. In this article
I will only cover some of the things that you will be able to do by
using a database as a backend that you would not be able to do (or at
least without any degree of difficulty or impracticality) using the
Ad Rotator component and its text file backend. The second major advantage
is that it is very easy to build a Web-based administration system for
the banners. This makes the administration of the system accessible
from anywhere in the world, and you no longer need to worry about version
control of the banner definitions.
The Basic Database Table Structure
We will expand on the table structure later, but for now we only
require one table – Banners – which will contain the
banner image location, the link URL and the alternate text. We
obviously want a unique ID for each banner, for administrative and
logging purposes, so here's the final structure:
Banners Database Structure |
|---|
| Field | Type [Access] | Type [SQL Server] |
|---|
BannerID | Autonumber | Integer [Identity] |
ImageSrc | Text [255] | varchar [255] |
LinkURL | Text [255] | varchar [255] |
ALT | Text [255] | varchar [255] |
The Banner Rotation Code
Again, I will expand on this later, but for the moment we'll keep this
code to the absolute minimum so you can easily understand the basics of
the system. What we want to do is grab all the banners definitions out
of the Banners table and store them in an array. We then
work out how many banner definitions there are, and generate a random
number accordingly (using the number of definitions as a maximum). This
will enable us to input an image (IMG) tag with the
randomly chosen banner into the page (using the random number as an
index in the banner definitions array).
<!-- #INCLUDE FILE="adovbs.inc"-->
<%
Dim objRS, arrBanners, intMax, intRand
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT ImageSrc, LinkURL, ALT FROM Banners", _
"DSN=BannerRotator", adOpenKeyset, _
adLockOptimistic, adCmdText
intMax = objRS.RecordCount
arrBanners = objRS.GetRows()
objRS.Close
Set objRS = Nothing
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>
|
Note: It is important not to omit setting the CursorType,
LockType and CommandType properties of the
Recordset object due to the use of the RecordCount method
later on. I have included them in the Open method of the
Recordset object.
| A Note from the Web Master... |
|---|
The RecordCount property is used to determine the number
of records in the Recordset, which is then used as the upper bound
for the random number picker (hence the need for the cursor type and lock type.
However, this can be avoided by using the UBound function on
the array (arrBanners).
|
This code also assumes that all your banner images are stored in a
subdirectory of the folder/directory that this file is in, called
/banners. This code is sufficient, so that if you create
the database and BannerRotator DSN and add a few entries
to the Banners table, it will work. However, this code does
not offer any advantages over Microsoft's AdRotator component, other
than that you can easily build a Web-based interface for adding,
editing and removing banner definitions. I will
cover the Web-based admin section later, but for now I'm going to
start showing you what you can do with this database backend.
Banner Categories
Most larger websites that use customized banner rotation systems have
different categories of banners for different sections of their sites. For
example, Yahoo! shows web hosting-related banners when you visit its Web
Hosting section in its directory, and golf-related banners when you visit
its Golf section. This can be a major selling point if you are aiming
to sell advertising, as you can offer advertisers targeted ads, so
that all of the impressions you're charging them for are being viewed
by people in their target audience.
The best way to go about this is to create a table that will store the
list of categories, and then be able to link it to the Banners table.
We'll call the new table Categories, and add one more
column to the Banners table, CategoryID, a
foreign key to the primary key in the Categories table.
Here are the new banner database table definitions:
Banners Database Structure |
|---|
| Field | Type [Access] | Type [SQL Server] |
|---|
BannerID | Autonumber | Integer [Identity] |
CategoryID | Number | Integer |
ImageSrc | Text [255] | varchar [255] |
LinkURL | Text [255] | varchar [255] |
ALT | Text [255] | varchar [255] |
Categories Database Structure |
|---|
| Field | Type [Access] | Type [SQL Server] |
|---|
CategoryID | Autonumber | Integer [Identity] |
Name | Text [255] | varchar [255] |
We now need to change our code to incorporate this enhancement. Firstly,
we need to know which category a specific page is going to use for its
banner rotator. I'm going to assume that it is passed as a querystring variable
called catid. I will discuss other options for automatically
determining the category later on.
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
Dim objRS, arrBanners, intMax, intRand
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT ImageSrc, LinkURL, ALT FROM Banners " & _
"WHERE CategoryID = " & Request.QueryString("catid"), _
"DSN=BannerRotator", adOpenKeyset, _
adLockOptimistic, adCmdText
intMax = objRS.RecordCount
arrBanners = objRS.GetRows()
objRS.Close
Set objRS = Nothing
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>
|
Let's assume you're building a web directory, and your Categories
table looks something like this:
CategoryID | Name |
|---|
| 1 | Computers |
| 2 | Sport |
| 3 | Media |
Your Web directory's home page would include links that look something
like this:
<A HREF="computers/index.asp?catid=1">Computers & Internet</A><BR>
<A HREF="sport/index.asp?catid=2">Sport</A><BR>
<A HREF="media/index.asp?catid=3">Media</A><BR>
|
You would also include code that says that if no category is
specified (either because you forgot to link to the file with a
querystring, or you did it intentionally), the banner rotator will
pull a banner from any category. You would do this by adding an
IF...THEN condition to generate the SQL query.
With that change, our code would now look like:
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
Dim objRS, arrBanners, intMax, intRand, strSQL
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT ImageSrc, LinkURL, ALT FROM Banners"
If CInt(Request.QueryString("catid")) <> 0 Then
strSQL = strSQL & " WHERE CategoryID = " & _
Request.QueryString("catid")
End If
objRS.Open strSQL, "DSN=BannerRotator", adOpenKeyset, _
adLockOptimistic, adCmdText
intMax = objRS.RecordCount
arrBanners = objRS.GetRows()
objRS.Close
Set objRS = Nothing
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>
|
That wraps up this first part of a two part series. In the second part
(which should be available by January 15th, 2001), we'll examine how to
enhance with our banner rotation script so that certain banners appear
when certain search terms are used to search the site! Until then,
happy programming!
By Peter McMahon
Read Part 2 of this Two-Part Article!
Attachments:
Download the complete source code in ZIP format
View the Banner Rotation Article Index
Read the Second Part of this article...
