This tutorial is intended for the PHP programmer interested in developing a useful and dynamic banner advertisement system using mySQL, PHPLIB, and PHP version 4.0 or higher.

Intended Audience

This tutorial is intended for the PHP programmer interested in developing a useful and dynamic banner advertisement system using mySQL, PHPLIB, and PHP version 4.0 or higher.

You will need a limited knowledge of mySQL including how to create a mySQL table using the mySQL client and must have PHPLIB version 7 operational.

Readers interested in learning more about mySQL and PHPLIB before reading this tutorial are encouraged to refer to:

• PHPLIB
• MySQL

Overview

With the growing importance of Web page real estate, meaning the actual space that defines your Web page, banner advertisements have evolved from being displayed statically to being displayed dynamically.

Dynamic banner display means that the same banner advertisement changes with each refresh of the page. This enables the display of a myriad of ads on a Web site without having to clutter a single Web page.

Moreover, banners can be a viable source of income. Statistical information reporting the number of "hits" (the number of times the banner is displayed) and "clicks" (times the banner is clicked on) can be generated to justify the use of each banner.

This tutorial provides instruction for developing a banner advertisement rotation system in PHP. Application scripts are based on a system running PHP 4.0 or higher with some sort of database to store our data (we will be using mySQL).

The banner advertisement script will enable you to:

• Automate rotation of a banner upon every refresh of the page.
• Keep track of banner statistics, including number of displays and actual clicks without the use of cookies, regardless of the banner destination.
• Switch banners in and out of active rotation without having to delete them from the database.

To achieve these features, this tutorial will demonstrate that PHP is capable of:

• Identifying each banner uniquely by assigning it a unique 32 byte ID.
• Give you the option to select the order in which the banners will be displayed.
• Generating the necessary HTML to display the banner correctly and dynamically based on the data provided and associated with the unique ID.
• Providing a means of tracking banner hits without interfering with its original destination link.

Note: Instruction for administering Rotating

Learning Objectives

In this tutorial, you will learn how to:

• Modify php.ini or use .htaccess files to auto-prepend scripts to all of your Web pages.
• Use PHPLIB's database class to perform queries against a mySQL database.
• Create a full-featured banner rotation system complete with active/inactive banners and automatic rotation.
• Generate hit/click statistics for each banner.

Definitions

• PHPLIB - A library of classes that provide a standardized way to perform many common tasks such as accessing a database and session management.
• MySQL – A fast and reliable open-source SQL database software that provides an efficient data storage for Web page data.
• Auto-prepend – A method of including PHP Code on every single page automatically.

Background Information

This tutorial relies on two software packages other than PHP. PHPLIB will be used to access the database needed for the script. This is done so that the script in this tutorial can be used regardless of the actual database software being used. For data storage, mySQL will be used as the database.

Note: Due to the flexibility of PHPLIB's database access, the code within this tutorial may work on other SQL databases with little or no SQL query modification. Consult the database software documentation as well as the PHPLIB manual for more information.

Prerequisites

As a prerequisite for creating a banner system, it is recommended that you:

• Define the database from which you will store and manage your banners' details. This tutorial utilizes mySQL as its DBMS of choice. You should define a banner's details, the database definition, and the mySQL statement.
• Configure PHP to automatically prepend a file.

Banner Details

Naturally, when structuring the database, the actual data associated with the banner follows a unique ID. The question is what data is really necessary and in what form should it take in the table?

For example, should the actual banner graphic be included within the database, say as a BLOB field, or should only the filename to the graphic be provided? For our tutorial, we have opted to include the banner graphic's filename as a string, represented by the column 'SRC'.

Note: The 'SRC' Field can only store a limited sized URL or file path; it is defined as VARCHAR (255).

The first field in our table structure is the ID field. This field serves as our unique banner identifier. Banner IDs can be defined using the statement below. In doing so, you can expect to receive a unique ID 32 characters in length:

The next three fields represent their HTML counterparts.

• HREF: Banner's URL, used as part of the tag.
• SRC: Graphic to be displayed when using the tag.
• ALT: Alternative banner description, when using the tag.

Note: From the fields listed above, only the ALT field is not mandatory and can be set to NULL.

The Hits and Clicks integer fields maintain display and click-through statistics respectively.

Two enumerated fields, Active and Pos, are used as follows:

• Pos field: Used as a flag field to indicate that this banner has already been displayed in this rotation cycle.
• Active field: Used to determine whether or not the banner is active.

The b_order integer field rounds out the table shown below. b_order defines the order in which a banner will be displayed. If you choose to display your banners in a particular sequence then this field will be mandatory. You would assign each banner a position and record it here.

Column	Descripton	Req'd?
ID	Unique Banner ID, a MD5 32 Character String	Y
HREF	The URL to use in the banner's tag	Y
SRC	The Graphic to use in the banner's tag	Y
ALT	The alternate description for the banner graphic. Used for	N
hits	The Hit (or display) stetisticsfor the banner	N
clicks	The Click statistics for the banner	N
active	Is this banner currently in active rotation?	Y
pos	This fields serves as a "last used" identifier for PHP to track what banner was last displayed	N
b_order	The Order the banner will be displayed	Y

The Database Definition

Displayed below is the mySQL table definition for the set of banner records.

Banner Table Definitions

Note: When defining your table ensure that the fields ID, URL, and file are set with the NOT NULL Property (or similar).

The mySQL Statement

The following CREATE TABLE statement may be used create the above table:

Configuring PHP to auto-prepend a file

You should configure PHP to auto-prepend your script's necessary code. By using auto-prepend you make the script available to any page on your Web site.

Place your finished script in a separate file and include it with the following statement in your php.ini or .htaccess file:

However, provided that:

• Your Web hosting provider does not permit you to modify the php.ini file or include a .htaccess file, or
• You already use the auto-prepend feature to include a file before all of your pages (such as the PHPLIB prepend.php3).

You can include your file before any output to the user is sent, using the following code:

How it works

Let's paint a scenario. You have created a Web page and would like to generate some advertising revenue by inserting a rotating banner advertisement somewhere on that page.

The script presented in this tutorial is a self-contained application. However, it must be included before any output is sent to the user. The entire script is contained within a single file (complete with the function to display the banner).

As such, to add a rotating banner:

• Include the script in your document using auto-prepend or an include() statement.
• Insert the following function call: display_banner(); Calling this function with no parameters will cause the script to simply pick the next appropriate banner (meaning the next active banner available) and display the HTML behind it. The two optional parameters, $bannerID and $query_extra are used under special-case circumstances outlined below.
  • $bannerID is used to force the script to display a specific banner based on its ID. When the specific banner is displayed, it does not affect the normal rotation pattern of the banners.
  • $query_extra is used to include specific, relevant variables along with the banner itself when a user clicks on it. $query_extra is an array and must be formatted in the following way:
    
        array("var1name"=>"var1value", "var2name"=>"var2value");

    This feature is for the case where you might want to display a banner that links to another section of your site. If you use Sessions on your site, a normal redirect could potentially lose the current session data (if a GET method is being used instead of cookies). To compensate for this, $query_extra should be appended to the end of the banner's URL. This would enable you to keep your session data intact. This feature also could be used to transfer any extra required data regarding your banner to an external site if the situation warrants.

  • Display_Banner()generates the proper HTML complete with a tag that points to $PHP_SELF and passes the proper banner ID back to itself (which then triggers the redirect logic).

Script Overview

With the scenario firmly in mind, the remainder of this section presents the contents of the banner script. The basic code-flow of the script is outlined below:

Step 1: Opening of page, Load prepended script file or execute include statement.

Step 2: Is a banner ID been given? If so, go to step 7. Otherwise, step 3.

Step 3: Load selected banner's data and add 1 to the banner's display statistics.

Step 4: Display HTML for banner using loaded information.

Step 5: Continue displaying page as normal.

Step 6: Reload the Web page and pass itself the banner ID of given banner clicked.

Step 7: Add 1 to the banner's click statistics.

Step 8: Redirect User to page specified in database for given banner ID.

What you need to do

• Open the page: Process a clicked banner if a banner ID is present. Otherwise, display the requested page as expected.
• Select a banner for display: Decide which banner to display.
• Generate the HTML: Output the required HTML.
• Special Case: Appending extra GET method data.

Open the Page

To track banner clicks, it is necessary to include the required code at the beginning of every page that will display the banner.

This code, nested within an if statement, is activated only when a banner has been clicked. Otherwise, the original page is displayed as expected.

Be sure to call the Display_Banner() function within the original page's script to display the banner at that page's preferred location. It is not necessary to call Display_Banner() on every page you include the auto-prepend script, rather only where you want a banner displayed.

Code Flow

• Check for the existence of $_bannerID (exists only if the user has clicked on a banner).
• Open a connection to the Database.
• Gather information about the banner from the database and validate the banner ID.
• Alter Statistics and re-direct user to the proper URL using the header() function.

Select a banner for display

Having already created an auto-prepend file (see Prerequisites), our task now is to process the data and generate the required HTML to display a given banner.

In the Script Flow presented below, the tag of the banner is set to point to itself (i.e. the same page). It passes the ID of the banner that was just displayed. When the user clicks on the banner and the page is re-loaded (with the passed banner ID) the auto-prepended code is triggered and the appropriate action is taken.

Note: If we were not concerned with statistics, this step would have been a simple matter of putting the correct URL in the tag.

The script features two banner selection methods:

• Default method: Banners are displayed in a predefined order based on the b_order column in the table.
• Forced method: The script forces the display a specific banner based on it's banner ID.

Both of these methods are accessed by calling the function display_banner(). This function takes two optional parameters - a string that would contain a specific banner ID and a second string for extra query parameters.

Note: If you would like the script to behave in the default manner but still pass extra parameters using $query_extra, you must set the first parameter of display_banner() to the value "none" (case sensitive).

Code Flow

• Check to see if a banner ID was provided
• Check for the existence of any banners in the database
• Check to see if there are banners that have not been displayed yet. If not, reset the database so that all the banners can be displayed.
• By default, retrieve all of the banners that have not yet been displayed, ordered by the b_order column. Otherwise, retrieve the banner based on the banner ID provided.
• Take the first row returned and use it as the next banner to be displayed
• Construct the proper HTML for the banner based on the database data and the $query_extra array.

  The $query_extra parameter of the display_banner() function is used to append extra GET method data to the end of the banner URL. This data should be an associative array representing the variables you would like to pass along with the banner ID when the banner is clicked (such as a session ID).

• If the banner was selected by rotation, meaning by default, flag the selected banner so that it will not be displayed twice in a row.

Generate the HTML

In this stage of the script, the HTML is generated. The following basic HTML format is used to display our banner – the fields encased in '%' symbols define where PHP will fill in appropriate values:

<IMG src="%SRC%" ALT="%ALT%" BORDER=0>

</A>

Note: Be aware that when extra data is passed along using the $query_extra parameter, it is appended in the proper format to the HREF field.

Once the banner has been displayed, the script ends unless the user clicks on the banner. In such a case, the variables $_bannerID and any data brought along with the $query_extra parameter is passed to itself (which in turn activates the source from the "Opening the page" section and takes the proper actions).

Code Flow

• Construct the HTML for the GET method.
• Make the extra query string.
• Construct the HTML for the banner and include the GET we constructed previously.
• Flag the banner.
• Echo the HTML.

$get  = "?_bannerid=";
$get .= urlencode($ban_data['ID']);

unset($query);

if(is_array($query_extra)) {      
   
   foreach($query_extra as $key => $val) {
   
      $get .= "&$key=".urlencode($val);
   
   }

}

$html = ""
      . "
      . "ALT='$ban_data[ALT] "
. "BORDER='$_bannercfg[border]'>"
      . "";


if($bannerID == "none") {

   $DB->query("UPDATE banner SET pos='A' 
    WHERE(ID='".$ban_data['ID']."')");
         
}

echo $html;

return true;

}
?>

Special Case: Appending extra GET method data

Sometimes it is necessary to pass data along with a banner that, although is important to a third party or script, is irrelvant to this particular banner script.

For example, assume that in order to receive credit for the click-through (we'll call this ID parameter "tracker" with a value of "foo"), you need to pass an ID code to a URL that is being pointed to by the banner.

In order to pass this ID, you would need to include it as the $query_extra parameter when calling Display_Banner()as follows.

Code Flow

• Place the tracker ID code into a associative array with the key set as the parameter name (tracker) and the value set at it's value (foo).
• Pass the array to Display_Banner() as it's second parameter

The Script

Note: The PHP elements are found within the marks.
The code contains comments preceded by // or /* and */ marks.

  // Check to see if the banner ID flag is set
  if(isset($_bannerID)) {
 
        // Create an instance of the Database Layer

     $DB = new DB_BANNER;
 
      // Get info about banner from DB

      $DB->query("SELECT * FROM banner 
 WHERE(uniqueid=$_bannerID)");
      
      // Check to see if Banner ID is valid before doing 
      // anything else

      if($DB->num_rows() != 0) {
         
         // Increment Click Counter

         dbIncClks($_bannerID);
         
         // Redirect user to new location, $_query_extra  
   // is the Extra parameters that were given to the 
   // script back in dbShowBan(). This feature is 
   // for if you want a banner on your own site, to 
   // your own site, but use sessions and don't want 
   // the user to lose their session when they 
   // click.

         $url = $DB->f("URL"). $_query_extra;
    Header("Location: $url");
         
      }
   
      
  }

function Display_Banner($bannerID = "none", 
  $query_extra = "") {

// Grab the Database Layer and the Config options

global $DB,  $_bannercfg, $PHP_SELF;

// Did we pass a BannerID?

if($bannerID == "none") {
         
   // If Not, Grab the next in rotation
   
   // First, make sure that there is something to select
   
   $DB->query("SELECT * FROM banner WHERE pos='' 
         LIMIT 1");

   // If there is nothing, then everything has 
   // been displayed
   // Time to start from square 1

   if($DB->num_rows() == 0) {
   
        $DB->query("UPDATE banner SET pos='' WHERE(pos='A')");
                
   }
   
   // Finally, select the next banner

   $DB->query("SELECT * FROM banner WHERE(pos='') 
    ORDER BY b_order LIMIT 1");
   
                
} else {
   
   // If so, Grab the requested Banner

   $DB->query("SELECT * FROM banner 
    WHERE(ID='$bannerID')");
   
                   
}

// Make sure we grabbed something

if($DB->num_rows() == 0) {

return false;
    }
   
$DB->next_record();

// Saved the data we got in a separate array

$ban_data = $DB->Record;

// Construct the HTML for the GET 

$get  = "?_bannerid=".urlencode($ban_data['ID']);

// Make the extra query string 

if(is_array($query_extra)) {      
   
   foreach($query_extra as $key => $val) {
   
      $get .= "&$key=".urlencode($val);
   
   }

}

// Construct the HTML for the banner and include the GET // we constructed previously.

$html = ""
      . "
      . "ALT='$ban_data[ALT] "
. "BORDER='$_bannercfg[border]'>"
      . "";

// Increment the hits
dbIncHits($ban_data['ID']);
     
// Flag the banner used as displayed (if it was selected 
// by rotation) In special cases, we're not going to flag 
// anything

if($bannerID == "none") {

   $DB->query("UPDATE banner SET pos='A' 
    WHERE(ID='".$ban_data['ID']."')");
         
}

// Finally, echo the HTML

echo $html;

// Return True

return true;

}

function dbIncClks($_bannerID) {

        global $DB;

$DB->query("SELECT clicks FROM banner 
WHERE(ID='$_bannerID')");

        If($DB->num_rows() == 0) {

return false;

        }

      $DB->query("UPDATE banner SET clicks=".($DB->f('clicks')+1)." WHERE(ID='$_bannerID')");


}

function dbIncHits($_bannerID) {

        global $DB;

$DB->query("SELECT hits FROM banner 
WHERE(ID='$_bannerID')");

        If($DB->num_rows() == 0) {

return false;
        }

      $DB->query("UPDATE banner SET hits=".($DB->f('clicks')+1)." WHERE(ID='$_bannerID')");


}

?>