Serving Banner Ads

17.9.1 Problem

You want to display banner ads by choosing images on the fly from a set of images.

17.9.2 Solution

Use a script that selects a random row from an image table and sends the image to the client.

17.9.3 Discussion

The script just shown assumes that the URL contains a parameter that names the image to be sent to the client. Another application might determine which image to display for itself. One popular image-related use for web programming is to serve banner advertisements for display in web pages. A simple way to do this is by means of a script that picks an image at random each time it is invoked. The following Python script,, shows how to do this, where the "ads" are the flag images in the image table:

#! /usr/bin/python
# - server randomly chosen banner ad from image table
# (sends no response if no image can be found)

import sys
sys.path.insert (0, "/usr/local/apache/lib/python")
import MySQLdb
import Cookbook

conn = Cookbook.connect ( )

 query = "SELECT type, data FROM image ORDER BY RAND( ) LIMIT 1"
 cursor = conn.cursor ( )
 cursor.execute (query)
 row = cursor.fetchone ( )
 cursor.close ( )
 if row is not None:
 (type, data) = row
 # Send image to client, preceded by Content-Type: and
 # Content-Length: headers. The Expires:, Cache-Control:, and
 # Pragma: headers help keep browsers from caching the image
 # and reusing it for sucessive requests for this script.
 print "Content-Type: %s" % type
 print "Content-Length: %s" % len (data)
 print "Expires: Sat, 01 Jan 2000 00:00:00 GMT"
 print "Cache-Control: no-cache"
 print "Pragma: no-cache"
 print ""
 print data
except MySQLdb.Error, e:

conn.close ( ) sends a few headers in addition to the usual Content-Type: and Content-Length: headers. The extra headers help keep browsers from caching the image. Expires: specifies a date in the past to tell the browser that the image is out of date. The Cache-Control: and Pragma: headers tell the browser not to cache the image. The script sends both headers because some browsers understand one, and some the other.

Why suppress caching? Because if you don't, the browser will send a request for only the first time it sees it in a link. On subsequent requests for the script, the brower will reuse the image, which rather defeats the intent of having each such link resolve to a randomly chosen image.

Install the script in your cgi-bin directory. Then, to place a banner in a web page, use an tag that invokes the script. For example, if the script is installed as /cgi-bin/, the following page references it to include an image below the introductory paragraph:

Banner Ad Test Page 1

You should see an image below this paragraph.

If you request this page, it should display an image, and you should see a succession of randomly chosen images each time you reload the page. (I am assuming here that you have loaded several images into the image table by now using the script discussed in Recipe 17.7. Otherwise you may not see any images at all!) If you modify not to send the cache-related headers, you likely will see the same image each time you reload the page.

The cache-control headers suppress caching for links to that occur over the course of successive page requests. Another complication occurs if multiple links to the script occur within the same page. The following page illustrates what happens:

Banner Ad Test Page 2

You should see two images below this paragraph, and they probably will be the same.

You should see two images below this paragraph, and they probably will be different.

The first two links to are identical. What you'll probably find when you request this page is that your browser will notice that fact, send only a single request to the web server, and use the image that is returned where both links appear in the page. As a result, the first two images displayed in the page will be identical. The second two links to show how to solve this problem. The links include some extra fluff at the end of the URLs that make them look different. doesn't use that information at all, but making the links look different fools the browser into sending two image requests. The result is that the second two images will differ from each otherunless happens to randomly select the same image both times, of course.

17.9.4 See Also

If you run a version of MySQL older than 3.23.2, the ORDER BY RAND( ) clause used in the image-selection query will fail. See Recipe 13.8 for a workaround.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: