TutorialStream.com - Complete affiliates management system

Complete affiliates management system

Published on: 2006-10-16 - Views: 8115

In this tutorial we'll see how to create an affiliates management system.
We will use it to add, edit and remove our affiliates.
We'll use a form to upload the button and all the features of each affiliate.
In this tutorial we'll build the whole system that let us add, edit and delete the affiliates.
I'll use "//**" to mark the point below which you'll need to add the code going ahead in the tutorial.
To make this code work you need to add a couple of lines to your .htaccess file and to have mod_rewrite on.
The "[-A-Za-z0-9_]+" means that you can use any combination of numbers, letters - and _ as affiliate code (see a few lines below what it is)

RewriteEngine On
RewriteRule ^/?affiliate/([-A-Za-z0-9_]+)/?$ affiliate.php?aff=$1

Let's start creating the database table where we'll store all the affiliates;
we'll have the following fields:
code = a string that represents the affiliate, no need to match the site name, it's a kind of alphanumeric ID
name = the real name of the website
url = the affiliate website url
img = the name of the affiliate's button
total = the number of visitors sent to that affiliate
genre = the genre of the affiliate (in case you have several categories of affiliates)
we set code, name, url and img as unique keys as we need not to have duplicates amongst them

CREATE TABLE `affiliates` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(20) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `img` varchar(255) NOT NULL default '',
  `total` int(11) NOT NULL default '0',
  `genre` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `url` (`url`),
  UNIQUE KEY `img` (`img`)
) TYPE=MyISAM;

I assume your page is named index.php.
Put this into your index.php: it checks if there are message to display and prints the menu with the 3 main options, Add, Edit and Delete(you'll see later the use of the message)

<?php if (isset($_GET["message"]))
{
  $message = $_GET["message"];
  echo "<div>$message</div>";
}
echo "<div>
<a href=\"index.php?action=addaffiliate\">Add Affiliate</a> | 
<a href=\"index.php?action=editaffiliate\">Edit Affiliate</a> | 
<a href=\"index.php?action=deleteaffiliate\">Delete Affiliate</a>
</div>";
//**
?>

This is the code that we'll use to display a form to add a new affiliate: (paste it into index.php below "//**")

if ($_GET["action"] == "addaffiliate")
echo
"<div><b>Add Affiliate</b>
<form method=\"post\" action="core.php?action=addaffiliate" enctype="multipart/form-data">
<ul>
  <li>URL: <input type=\"text\" name=\"URL\" value=\"http://www.\" /></li>
  <li>Code: <input type=\"text\" name=\"code\" value=\"\" /></li>
  <li>Name: <input type=\"text\" name=\"name\" value=\"\" /></li>
  <li><input type=\"hidden\" name=\"MAX_FILE_SIZE\" value=\"200000\"></li>
  <li>Button: <input type=\"file\" name=\"button\"></li>
  <li><select name=\"genre\"><option>partners</option>
<option>standard</option>
<option>tutorialsearch</option>
<option>designrelated</option>
</select></li>
<li><input type=\"submit\" name=\"Submit\" value=\"Add\" /></li>
</form>
</div>";

In this form you'll be able to specify all the fields required for a new affiliate, the URL field will automatically display the http://www. string, the hidden field is used to specify the maximum file size of the button that in this case is set to 200000 (remember that the MX_FILE_SIZE is in bytes), the genre select field is a drop down that lists all the genres you want to be available, just type the ones you need.
As you can see the form forwards the values to a page named core.php; create a file named core.php and place it in the same folder of your index.php: in this example both the files are into a folder named "admin" in the document root folder (public_html, htdocs, www or what your hosts chose). Let's see it.

<?php
$host = 'mysqlhost'; 
$username = 'mysqluser';
$database = 'mysqldatabase';
$password = 'mysqlpassword';
$connect = mysql_connect($host,$username,$password) or die("Error connecting to Database! " 
. mysql_error());
mysql_select_db($database , $connect) or die("Cannot select database! "
. mysql_error());
if ($_GET["action"] == "addaffiliate")
  { 
    if((isset($_POST["MAX_FILE_SIZE"]))&&(isset($_POST["name"]))&&(isset($_POST["URL"]))
&&(isset($_POST["genre"]))&&(isset($_POST["code"])))
    {
      $tmp_name = $HTTP_POST_FILES['button']['tmp_name'];
      $name = $_POST["name"];
      $code = $_POST["code"];
      $genre = $_POST["genre"];
      $URL = $_POST["URL"]; 
      $filename = $_FILES['button']['name'];
      if(move_uploaded_file($tmp_name, "../images/affiliates/$filename"))
      {
        $query =  "INSERT INTO affiliates (id, code, name, url, img, genre) values(NULL, '$code' ,
 '$name', '$URL', '$filename' ,'$genre')";
        $go = mysql_query($query)or die(mysql_error());
        if($go)
        {
        	header("Refresh: 0; url=/admin/index.php?message=Affiliate added");
        }
      }  
    }
  }
  //**
?>

This piece of code checks if the action is "addaffiliate" and, in this case, checks if all the required fields have been filled, after that copies all the variables from the $_POST array into standard variables (just to make it compatible with servers where register_globals is set to off), moves the just uploaded image to the folder images/affiliates and runs a query that adds the affiliate to the database, if the query is successful the page headers are refreshed to the index, with a message that confirms that the operation was successful.
At this point the first of the three parts is completed, we can add our affiliates without any need to use an FTP client or go to phpmyadmin.
Let's see how to edit an affiliate.
This portion of code is used when you click "Edit Affiliate" and must be pasted below "//**" into index.php
It will print a table containing all the affiliates in the database so that we'll be able to click on the one we want to edit.
Long urls will be trimmed to 40 chars, you can edit it in the line where the variable $shorturl is declared.
After we have clicked on one of the affiliates the page will be reloaded and the "affiliatetoedit" parameter will be sent as a $_GET value; after this a query will be run to retrieve all the fields of the chosen affiliate and a form will be displayed, with the fields already populated. Here you'll be able to apply modifications and once you click the "Save" the new values will be sent to the core.php page.

if ($_GET["action"] == "editaffiliate")
{
  $host = 'mysqlhost'; 
  $username = 'mysqluser';
  $database = 'mysqldatabase';
  $password = 'mysqlpassword';
  $connect = mysql_connect($host,$username,$password) or die("Error connecting to Database! " 
. mysql_error());
  mysql_select_db($database , $connect) or die("Cannot select database! " . mysql_error());
  if(!isset($_GET["affiliatetoedit"]))
  {
    $query = "select * from affiliates";
    echo "<div><b>Edit Affiliates</b><br/><br/>";
    echo "<table><tr><td>ID</td><td>Code</td><td>Name</td><td>URL</td><td>Image</td>
<td>Genre</td><td></td></tr>";
    $runquery = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_object($runquery))
    {
      $shorturl = substr($row->url, 0, 40);
      if ($shorturl<$row->url)
      {
        $shorturl = $shorturl."...";
      }
      echo "<tr><td>$row->id</td><td>$row->code</td><td>$row->name</td><td>$shorturl</td>
<td>$row->image</td><td>$row->genre</td><td>
<a href=\"index.php?action=editaffiliate&affiliatetoedit=$row->id\">Edit</a></td></tr>";
    }
    echo "</table>";
  }
  else
  {
  $affiliatetoedit = $_GET["affiliatetoedit"];
  $query = "select * from affiliates where id='$affiliatetoedit'";
  $runquery = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_object($runquery))
    {
      echo "<div><form name=\"form1\" method=\"post\" action=\"core.php?action=editaffiliate\">
      <ul>
        <li><input type=\"hidden\" name=\"id\" value=\"$row->id\" /></li>
        <li><input type=\"text\" name=\"code\" value=\"$row->code\" /></li>
        <li><input type=\"text\" name=\"name\" value=\"$row->name\" /></li>
        <li><input type=\"text\" name=\"url\" value=\"$row->url\" /></li>
        >li><input type=\"text\" name=\"image\" value=\"$row->img\" /></li>
        <li><select name=\"genre\"><option>partners</option>
<option>standard</option>
<option>tutorialsearch</option>
<option>designrelated</option></select<</li>
<li><input type=\"submit\" name=\"Edit\" value=\"Save\" /></li>
      </form>
</div>";
    }    
  }
}

Now we'll see what the core.php page does when it gets the results of the "Edit Affiliates" form, paste this code below "//**" in core.php,

if ($_GET["action"] == "editaffiliate")
  { 
    if((isset($_POST["name"]))&&(isset($_POST["id"]))&&(isset($_POST["url"]))
&&(isset($_POST["genre"]))&&(isset($_POST["image"]))&&(isset($_POST["code"])))
    {
      $id = $_POST["id"];
      $name = $_POST["name"];
      $code = $_POST["code"];
      $image = $_POST["image"];
      $url = $_POST["url"];
      $genre = $_POST["genre"]; 
      $a = "UPDATE affiliates SET name='$name', code='$code',img='$image', url='$url',
 genre='$genre' where id='$id'";
      $go = mysql_query($a)or die(mysql_error());
      if($go)
      {
      	header("Refresh: 0; url=/admin/index.php?message=Affiliate edited");
      }
    }
    else
    {
      echo "Fill all the fields";
    }
  }

It assignes the $_POST values to some variables (always to work with register_globals off) then runs a query that updates the values in the database and, if the update goes smooth, refreshes the headers to index.php with a message.
Now we'll see how to delete an affiliate, paste this code into index.php

if ($_GET["action"] == "deleteaffiliate")
{
  $host = 'mysqlhost'; 
  $username = 'mysqluser';
  $database = 'mysqldatabase';
  $password = 'mysqlpassword';
  $connect = mysql_connect($host,$username,$password) or die("Error connecting to Database! " 
. mysql_error());
  mysql_select_db($database , $connect) or die("Cannot select database! " . mysql_error());
  if(!isset($_GET["affiliatetodelete"]))
  {
    $query = "select * from affiliates ORDER BY name ASC";
    echo "<div><b>Delete Affiliates</b><br/><br/>";
    echo "<table><tr><td>ID</td><td>Name</td><td>Genre</td><td></td></tr>";
    $runquery = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_object($runquery))
    {
      echo "<tr><td>$row->id</td><td>$row->name</td><td>$row->genre</td><td>
<a href=\"index.php?action=deleteaffiliate&affiliatetodelete=$row->id\">Delete</a></td></tr>";
    }
    echo "</table></div>";
  }
  else
  {
    $affiliatetodelete = $_GET["affiliatetodelete"];
    $query = "delete from affiliates where id='$affiliatetodelete' limit 1";
    $runquery = mysql_query($query) or die(mysql_error());
    if($runquery)
      {
      	header("Refresh: 0; url=/admin/index.php?message=Affiliate deleted");
      }
  }
}

It retrieves all the affiliates from the database and prints a table containing them all, each affiliate has a link to delete it.
Once clicked on the Delete link the page is reloaded with "affiliatetodelete" as $_GET parameter, then a query is run and the affiliate is removed from the database.
Now we have a fully working affiliate management system, but we still don't have a page to display them.
Create a php file and name it affiliates.php, then paste this code into it.
Obviously this code prints the categories contained into the TutorialStream database, you should change the genres to your ones.

<?php
  $host = 'mysqlhost'; 
  $username = 'mysqluser';
  $database = 'mysqldatabase';
  $password = 'mysqlpassword';
  echo "<div><b>Tutorial Search Engines</b><br/>";
  $result = mysql_query("SELECT * FROM affiliates where genre=\"tutorialsearch\"ORDER BY 
total DESC");
  while ($row = mysql_fetch_object($result)) 
  {  
    echo "<a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "<img src=\"images/affiliates/$row->img\" alt=\"$row->name\"/></a> ";
    echo "<span><a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "$row->name</a> </span>";
    echo "<span>Clicks: ".$row->total."</span><br/>";
  }
  echo "<b>Standard Affiliates</b><br/>";
  $result = mysql_query("SELECT * FROM affiliates where genre=\"standard\"ORDER BY 
total DESC");
  while ($row = mysql_fetch_object($result)) 
  {  
    echo "<a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "<img src=\"images/affiliates/$row->img\" alt=\"$row->name\"/></a> ";
    echo "<span><a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "$row->name</a> </span>";
    echo "<span>Clicks: ".$row->total."</span><br/>";
  }
  echo "<b>Cool Design Sites</b><br/>";
  $result = mysql_query("SELECT * FROM affiliates where genre=\"designrelated\"ORDER BY 
total DESC");
  while ($row = mysql_fetch_object($result)) 
  {  
    echo "<a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "<img src=\"images/affiliates/$row->img\" alt=\"$row->name\"/></a> ";
    echo "<span><a href=\"affiliate/$row->code/\" target=\"_blank\">";
    echo "$row->name</a> </span>";
    echo "<span>Clicks: ".$row->total."</span><br/>";
  }
  echo "</div>";
?>

Those links might look weird since they point to something like affiliate/affiliatecode/ but those 2 lies we've added to our .htaccess file tell apache to point to the url affiliate.php?aff=$1 (where $1 is affiliatecode), now we just need to create the page affiliate.php in order to make it push the visitor to the effective affiliate url.
Note that this is a separate file named affiliate.php and not affiliates.php. Let's have a look at affiliate.php:

<?php
  $host = 'mysqlhost'; 
  $username = 'mysqluser';
  $database = 'mysqldatabase';
  $password = 'mysqlpassword';
  $connect = mysql_connect($host,$username,$password) or die("Error connecting to Database! " 
. mysql_error());
  mysql_select_db($database , $connect) or die("Cannot select database! " . mysql_error());
     if (isset($_GET['aff']))
     {
       $dest = $_GET['aff'];
       $query = mysql_query("SELECT url FROM affiliates where code='$dest' LIMIT 1") 
or die(mysql_error());
       $count = mysql_query("UPDATE affiliates SET total = total + 1 WHERE code='$dest'") 
or die(mysql_error());
       while ($row = mysql_fetch_object($query))
       header("Refresh: 0; url=".$row->url);
     }
     else
     {
       echo "Huh, something was wrong...";
     }
?>

This page is really simple to understand, it takes the affiliate code passed as $_GET paramenter ("aff"), runs a query to retrieve the url of the affiliate and pushes the visitor to that url refreshing the page headers.
Here we go, you have a complete affiliates management system, almost the same I'm using here at TutorialStream.com, I hope you like this tutorial.