Display Coupon Codes

This article will explain how to build a simple Perl application to display coupon codes on your website. I chose Perl because I really enjoy programming with it, much more so than working with PHP or other languages. However, this technique can really be implemented using any language. It really isn't the language used that is important.

There are a few steps involved, although they are rather trivial as far as programming goes. It is necessary to understand something about Perl or PHP, and it is essential to have some knowledge of phpMyAdmin. If you haven't worked with databases before, you can get a basic hosting account that provides at least one MySQL database as part of your hosting package.

Tools and Software

  • phpMyAdmin - an application you can install on your web hosting account that provides a rather primitive, but effective, interface to your mySQL database. phpMyAdmin provides features for easily creating, deleting, and modifying your database structures (schemas).
  • mySQL - mySQL is an excellent database for storing and managing data. It provides all the features you'll ever need for developing robust applications. mySQL is a standard package available on nearly all web hosting accounts.
  • Perl - this programming language has been around for years. Although I've read blogs knocking Perl in favor of PHP, the biggest problem people have with Perl is the installation of Perl libraries. There are hundreds, if not thousands, of Perl libraries available to do nearly everything and anything imaginable. Unfortunately, installing Perl libaries is not a trivial task, but once you understand where to look and how to install them, it can be easy.
  • Affiliate Network - Membership to an affiliate program such as Commission Junction (to get paid for your referrals). Here are the best affiliate programs to join.
  • Merchant - Signup with a merchant that you can promote. I'll use HP as an example because the information they provide to affiliates makes it exceptionally easy to maintain your coupon database and the products you are promoting are of the best quality.

Setting Up Your Database Table

This schema shown here is rather simple and I've been using and fine-tuning it for a while now. The goal is to be able to easily and quickly add new coupons, mark coupons as expired, remove coupons from the site, and auto generate affiliate links from your coupon database table.

coupon table schema

Field Descriptions

  • coupon_id: Auto-increment field used as the primary key for the table. When adding a new record to the table using phpMyAdmin, this key will be auto-generated for you, which helps speed up adding coupons to the table.
  • code: This field is shown as medium text but really could be just a varchar 50. I've never really seen a coupon code greater than 50 characters long. Most coupon codes are around 10 characters long.
  • offer_type: This field is setup as an enumerated type so that when adding a new coupon, it can be categorized. This allows your coupons to be organized on the page as different types.
  • category: This field is setup as an enumerated type so that when adding a new coupon, it can be categorized. This allows your coupons to be organized on the page as different categories.
  • description: This is a medium text field to contain enough space to accurately describe the offer.
  • date_posted: This date field is used to post the starting date of the coupon offer.
  • date_expires: This field was setup to hold either a date or special conditions when the offer expires. Not all coupons are based on a date. Some are based on the number of coupons used or other non-date criteria.
  • featured: This a flag in the table that allows an application to pull certain coupons that you might want to display on a main coupon page on your site to highlight certain offers.
  • merchant_id: This an optional part of the database table design. The merchant id is used as a foreign key to point into a table of merchants that you promote on your webste. The merchant table contains more information about a particular merchant. This is optional and is not required to implement a coupon database unless you are promoting coupons from different merchants on different web pages. In my table, however, it is required because I also pull additional information about the merchant when I display the coupons.
  • affiliate_url: This is special affiliate tracking URL that would be used. It is provided by the merchant and it is unique to your site. This URL contains the code that tracks a buyer to your affiliate account.
  • site_id: This is an optional part of the database table. In my table it is required because I have coupons belonging to different sites.
  • expired: This is a flag that I set by default to 'N'. If the coupon code expires, I go into the table and set this flag. I don't always want to delete the coupon when it expires for various reasons.

Work Flow

Once you've signed up with a Merchant through your Publishing Network (such as Commission Junction), the really good merchants will send you an email containing their latest coupon offers. The email will contain a link directly to your Commission Junction account which will display all the necessary codes you need to easily update your coupon table.

Here is an example email that HP sends every week.

The link at the bottom of this email is the affiliate URL. Once you add your Affilite Site ID (which you can find in the Commission Junction account for each website that you own), this affiliate code will be added to the affiliate_url (which is the code to link buyers to your affiliate account) field in your coupon table.

Data Preparation

Launch phpMyAdmin, select your coupon database, create a new table that we'll name Coupons, and click the insert record tab to create a new coupon code. You can highlight the description and copy directly from the email and paste it into your description field. Sometimes the email contains the start date and end date information. In this case, the start date will be the date you add the coupon to the database (because there aren't any specific instructions about the when the coupon code will be valid), and the end date is described in the description (offer limited to the first 1,700 coupons redeemed). Add any additional information about the coupon that you've setup in your MySQL coupon table.

Save the coupon record (you might want to create a few records) to the database and it is ready to be displayed on your site. In this example, there is no coupon code. If someone clicks the link on your page, the coupon will be automatically applied. In cases like this, I add a string in the coupon code stating that the link auto-activates the deal, which is why I have setup the code field to allow longer strings than the typical size of coupon code text. Some coupon offers will have specific coupon codes that must be entered into the merchant shopping cart when checking out in order to qualify for the deal.

Displaying Coupon Codes on Your Website

It really depends on the technology you're comfortable with, but some options for displaying the coupons on your site is to have your pages coded using PHP, use a WordPress based site with a customized plugin to display information from database tables, use a CMS that can be customized, or use SSI on static pages. I'll use SSI as an example because I wanted to show the Perl code used to create a plugin that will format and display your coupon codes.

Building A Perl Plugin

Access to a MySQL database is the first step to setup. When working with Perl, I save all my database connection information in a separate package file above the root folder of the web server, making access to the database connection information impossible to retrieve unless access is made through an application that I specifically build. The script will be a simple CGI program that will expect one parameter to be passed. In preparation for building the application, I will need to setup a database connection package that can be easily included in the script code.

Database Connection

package CouponDatabase;
use strict;
use DBI;my $host_name="HOST";
my $db_name="DATABASE";
my $dsn="DBI:mysql:host=$host_name;database=$db_name";
sub connect {
  return (DBI->connect ($dsn, "USERNAME", "PASSWORD", {PrintError=>0,RaiseError=>0}));
}
1;

We'll save this file as CouponBuilder.pm. The CouponDatabase package is terminated with a "1" to signify to the end of the package. Each application will likely have its own database connection package unless you have many different tables in the same database. In reality, you probably wouldn't have just a coupon database because displaying coupons will probably be a part of a much larger database solution. In this example, however, we'll assume that the coupon database is the only database we have to work with. You can use an FTP program to load this file up to your webserver in the server root and in folder named something like "MYSQLDB". The path on a Unix webserver would look something like "/MYSQLDB/CouponBuilder.pm" in the root directory of your webserver (not the root directory of your website). It doesn't matter where this folder is installed as long as it is not accessable from the starting folder of your web pages.

Program Logic

The logic of the application is pretty simple. The application will expect a command line parameter that we'll name 'm' to represent the merchant_id from our database schema above. We'll check for this parameter and if it doesn't exist, we'll simply exit. We won't display an error message of any kind because this type of application error is not for human consumption and we don't want to expose anything about the application. Our logic will go something like this:

IF there is no 'm' parameter
  exit
ELSE
  sanitize the input parameter and do some sanity checks
  set row_count=0
  open database
  retrieve all coupons where merchant_id = 'm'
  FOR each coupon row retrieved
    increment row_count
    print coupon row
  EndFor
  close database
EndElse
IF row_count = 0
  print "No coupons currently available"
EndIF

Implementation (Web Page)

We'll be using SSI (server side includes) as the method to retrieve and display coupon codes in a web page to show a simple implementation strategy. An SSI command in a web page looks like this:

<p>This is an example of a paragraph above where the coupons will display.</p>
<!--#include virtual="/cgi-bin/insert-coupons.cgi?m=14" -->
<p>This is an example of a paragraph below where the coupons will display.</p>

The para tags above and below the SSI command are to show where the SSI statement is placed and will display in the web page as normal paragraphs of text. The SSI shown points to the coupon insert application and will be named insert-coupons.cgi. The 'm' parameter is the merchant_id from our database schema above lets me pull all coupons for a particular merchant into the page. You would enter these HTML codes into your web page exactly as they are shown above. When browsers access a page with embedded SSI code, the server first parses and resolves the SSI statements. In this case, we are telling the server to run a script and add the ouput of the script exactly where the SSI code occurs in the page. The SSI command will be replaced with the generated output from the coupon application. Servers are not typically configured to allow SSI so depending on your own server configuration, you may have to figure out how to enable it. Also, when servers are configured to allow SSI, many people think that the page name extension must be .shtml, but with a minor update to the Apache config file, any file extension can be added to allow SSI pre-processing.

Implementation (application)

I'll only show the really essential code below to keep everything as clean and simple as possible.

   #!/usr/bin/perl
   use lib qw(/MYSQLDB);
   use CGI;
   use DBI;
   use CouponBuilder;
   use strict;

   #print out mandatory content type
   print "Content-Type: text/html\n\n";

   #get query input
   my $query = new CGI;
   my %in = &parse_form; #a function not shown that parses the command line and breaks out parameters

   my $DBH = WebDB::connect();
   my $db_coupons = "Coupons";
   my $sanitized_merchant = $in{'m'}; #sanitize the input with your own function

   my $stmt = qq {SELECT * FROM $db_coupons WHERE merchant_id=$sanitized_merchant ORDER BY date_posted DESC};
   my $sth = $DBH->prepare ($stmt);
   $sth->execute();

   #we'll print the coupons in a table so output the table header stuff
   print qq|<table summary="Coupons / Promotions" width="100%">
    <thead> 
    <tr> 
      <th>Type/Category</th> 
      <th>Latest Offer</th> 
      <th>Posted</th> 
      <th>Expires</th> 
      <th>Coupon</th> 
      <th>Action</th> 
    </tr> 
    </thead><tbody>
    |;

    while (my $coupon_row = $sth->fetchrow_hashref())
    {
      $row_count++;
      
      #print out each row
      print qq|
        <tr> 
          <td>$coupon_row->{'offer_type'}<br />$coupon_row->{'category'}</td>
          <td>$coupon_row->{'description'}
      |;

      if ($coupon_row->{'expired'} eq "Y") {
        print qq|<br /><strong><div class="notice">THIS OFFER HAS EXPIRED</div></strong>|;
      }
      
      print qq|</td> 
        <td>$coupon_row->{'date_posted'}</td> 
        <td>$coupon_row->{'date_expires'}</td> 
        <td>$coupon_row->{'code'}</td> 
        <td><a href="$coupon_row->{'affiliate_url'}">SAVE NOW</a></td> 
      </tr>|;
    }
    print qq|</tbody></table>|;

    $sth->finish ();
    $DBH->disconnect();

    if ($row_count == 0) {
      print qq|<p>Database is updating. Please try again later.</p>|;
    }

    exit 1;

When a browser requests the page that has the SSI code in it, the server will parse the SSI, run the script, and the output of the script will appear in the page where the SSI command occurred. Then the server will pass back the page to the browser requesting it. What the user sees is the finished page with the embedded output from the SSI command, as in the example on the HP Discounts page of this site.

Not shown is the function for handling the input parameters and the function to sanitize the parameters. Anytime an input value is used to run a query on a table it should be completely sanitized first. Here is an article on SQL Injection that explains more about it.

Build Websites | Contact Us | Sitemap | Terms & Conditions | Privacy Policy | Disclaimer | Support Center | Disclosure Policy | Link to Us | Blog