Affiliate Systems are useful for people who want to have affiliates on their sites to help boost sales.
Affiliates sign up and are given a link that they can use to redirect visitors to your site. The script will track clicks from each affiliate and also record any sales that come out of those clicks.
What am I going to show you? Here’s a brief overview.
We’re going to build a simple affiliate system that can be used on sites. You add a script to your site then allows people to sign up as affiliates and sell your products.
{mospagebreak title=Building The Database}
First, we want to build the database. This is a pretty basic database designed to keep track of affiliates and their traffic/sales to your website. We’ll need two tables.
CREATE TABLE affiliates (
ID int(10) NOT NULL auto_increment,
username varchar(50) NOT NULL default ”,
password varchar(32) NOT NULL default ”,
site varchar(255) NOT NULL default ”,
email varchar(255) NOT NULL default ”,
akey varchar(32) NOT NULL default ”,
ptype varchar(32) NOT NULL default ‘0’,
pemail varchar(255) NOT NULL default ”,
address text NOT NULL,
PRIMARY KEY (ID),
UNIQUE KEY akey (akey)
) TYPE=MyISAM AUTO_INCREMENT=1;
CREATE TABLE affstats (
ID int(10) NOT NULL auto_increment,
akey varchar(32) NOT NULL default ”,
type tinyint(4) NOT NULL default ‘0’,
status tinyint(4) NOT NULL default ‘0’,
ip varchar(150) NOT NULL default ”,
date datetime NOT NULL,
OrderID varchar(32) NOT NULL default ”,
price varchar(32) NOT NULL default ”,
PRIMARY KEY (ID)
) TYPE=MyISAM AUTO_INCREMENT=1;
Setting Your Settings
Your settings will be stored in a file called “config.php.” We’ll keep this simple.
<?
$sitename = “”; // site name
$siteurl = “”; // site url
$redirecturl = “”; // redirect url
$affpay = “”; // affiliate payout amount
$aduser = “”; // admin username
$adpass = “”; // admin password
$dbhost = “”; // database hostname
$dbuser = “”; // database username
$dbpassword = “”; // database password
$dbname = “”; // database name
?>
These settings are important and should be set before we start. The variables are broken down as follows:
$sitename = The name of your website
$siteurl = The URL to the affiliate script as it is
installed on your site
$redirecturl = The URL where visitors are redirected to after
coming to your site using an affiliate link.
$affpay = The amount affiliates are paid for each sale.
If you set it to 5.00, then affiliates
will get paid 5.00 for each sale.
$aduser = Your management login username
$adpass = Your management login password
$dbhost = Your database host (usually localhost)
$dbuser = Your database username
$dbpassword = Your database password
$dbname = The name of your database
{mospagebreak title=Being Common}
Now, we’re also going to create a file called “common.php.” This file is very important as it contains functions used by the script.
This script is being built so that the common file is the brains of the whole system.
<?
session_start();
include(“config.php”);
$dbh=mysql_connect ($dbhost, $dbuser, $dbpassword) or die (‘I cannot connect to the database because: ‘ . mysql_error());
mysql_select_db ($dbname,$dbh);
define(“DBH”,$dbh);
$affiliateForm = array(
“username”=>array(“User Name”,”text”,””),
“password”=>array(“Password”,”password”,””),
“site”=>array(“Your Web Site”,”text”,”http://”),
“email”=>array(“Your Email”,”text”,”yourname@yoursite.com“),
“akey”=>array(“”,”hidden”,””),
“ptype”=>array(“Payment Method”,”select”,”check|paypal”),
“pemail”=>array(“Paypal Email”,”text”,””),
“address”=>array(“Your Address”,”textarea”,””)
);
function makeKey($len){
$chars=
‘ABCDEFGHIJKLMNOPQRSTUVWXYZ
abcdefghijklmnopqrstuv
wxyz0123456789′; // characters to use in the password
mt_srand((double)microtime()*1000000^getmypid());
while(strlen($password)<$len){
$password.=substr($chars,(mt_rand()%strlen($chars)),1);
}
return $password;
}
function AffiliateAdd(){
$_POST[‘akey’] = makeKey(10);
unset($_POST[‘ID’]);
unset($_POST[‘step’]);
$query = “INSERT INTO affiliates (“.implode(“, “,array_keys($_POST)).”) VALUES (‘”.implode(“‘, ‘”,array_map(“mysql_real_escape_string”,$_POST)).”‘)”;
mysql_query($query,DBH) or die( mysql_error() );
return mysql_insert_id();
}
function AffiliateUpdate($aid){
unset($_POST[‘step’]);
$query = “UPDATE affiliates SET “;
foreach($_POST as $field => $value) {
$query .= “$field = “”.mysql_real_escape_string($value).””, “;
}
$query = substr($query, 0, strlen($query)-2).” WHERE ID = ‘{$aid}'”;
mysql_query($query,DBH) or die( mysql_error() );
}
function AffiliateStats($aid=””){
$sql = “SELECT * FROM affiliates WHERE ID='{$aid}'”;
$lo = mysql_query( $sql );
$affi = mysql_fetch_assoc($lo);
list($clicks) = mysql_fetch_row( mysql_query(“SELECT COUNT(*) FROM affstats WHERE akey= ‘{$affi[‘akey’]}'”) );
list($orders) = mysql_fetch_row( mysql_query(“SELECT COUNT(*) FROM affstats WHERE akey= ‘{$affi[‘akey’]}’ AND type=2”) );
list($pending_total) = mysql_fetch_row( mysql_query(“SELECT SUM(price) FROM affstats WHERE akey= ‘{$affi[‘akey’]}’ AND type=2 AND paid=0”) );
list($paid_total) = mysql_fetch_row( mysql_query(“SELECT SUM(price) FROM affstats WHERE akey= ‘{$affi[‘akey’]}’ AND type=2 AND paid=1”) );
$total = $clicks+$orders;
$order_total = $pending_total+$paid_total;
$return = array(
“total”=>$total,
“clicks”=>$clicks,
“orders”=>$orders,
“pending_total”=>$pending_total,
“paid_total”=>$paid_total,
“order_total”=>$order_total
);
return $return;
}
?>
Most of these functions are pretty straightforward; you can figure them out just from their names.
Look And Feel
There are two files that need to be created to give the site its look and feel.
The first one is “header.php.” This is the header for the site; change it to integrate more with your existing site:
<html>
<head>
<title><?=$sitename?> Affiliate Center</title>
</head>
<body>
<h1><?=$sitename?> Affiliate Center</h1>
Finally, we have “footer.php”, this is the other half of the look and feel of your site:
<hr/>
copyright 20016, <?=$sitename?>, <?=$siteurl?>
These can be changed quite easily, and changing them changes the whole look of the affiliate script.
{mospagebreak title=Your Index Page}
Create a page called “index.php.” This page serves as your introduction to your affiliate site.
<?
include(“common.php”);
include(“header.php”);
?>
<P>Sign up here to be one of our affiliates. Earn $<?=number_format($affpay,2)?> for each sale you
generate.</P>
<ul>
<li><a href=’account.php’>Login To Your Account</a>
<li><a href=’join.php’>Sign Up For An Account</a>
</ul>
<?
include(“footer.php”);
?>
Letting Affiliates Join
One of the first things you need to do is let your affiliates sign up.
Create a file called “join.php”:
<?
include(“common.php”);
include(“header.php”);
$step = isset($_POST[‘step’]) ? $_POST[‘step’] : 1;
switch ($step){
case ‘2’:
// add affiliate to the DB
$aid = AffiliateAdd();
if( $aid ){
echo “Thank you for signing up. You can now log in to your account<br>”;
echo “<a href=’account.php’>Login Here</a><br/>”;
}
break;
default:
?>
<table>
<form method=”POST”>
<input type=’hidden’ name=’step’ value=’2’>
<?
foreach($affiliateForm as $field=>$info){
echo “<tr>”;
if( !empty($info[0]) ){
echo “<td valign=top>{$info[0]}</td>”;
}
echo “<td valign=top>”;
switch($info[1]){
case “hidden”:
echo “<input type=’hidden’ name='{$field}’ value='{$info[2]}’>”;
break;
case “password”:
echo “<input type=’password’ name='{$field}’ value=”>”;
break;
case “text”:
echo “<input type=’text’ name='{$field}’ value='{$info[2]}’>”;
break;
case “textarea”:
echo “<textarea name='{$field}’ rows=5 cols=25>{$info[2]}</textarea>”;
break;
case “select”:
$info[2] = explode(“|”,$info[2]);
echo “<select name='{$field}’>”;
foreach($info[2] as $k=>$v){
echo “<option>{$v}</option>”;
}
echo “</select>”;
break;
}
echo “</td>”;
echo “</tr>”;
}
?>
<tr>
<td colspan=2><input type=’submit’ value=’Save’></td>
</tr>
</form>
</table>
<?
break;
}
include(“footer.php”);
?>
{mospagebreak title=Letting Affiliates Log in}
Okay, affiliates are able to sign up. Now, we need to let them log in to their accounts. We’ll now create two files for that.
The first file is called “login.php”:
<?php
session_start();
$auid = isset($_POST[‘auid’]) ? $_POST[‘auid’] : $_SESSION[‘auid’];
$pwd = isset($_POST[‘pwd’]) ? $_POST[‘pwd’] : $_SESSION[‘pwd’];
if(!isset($auid)) {
loginform();
exit;
}
$results = mysql_query(“SELECT * FROM affiliates WHERE username='{$auid}’ && password='{$pwd}'”);
$row = mysql_fetch_assoc($results);
if($row[‘username’] == $auid && $row[‘password’] == $pwd){
$_SESSION[‘userId’] = $row[‘ID’];
$_SESSION[‘username’] = $row[‘username’];
$_SESSION[’email’] = $row[’email’];
$_SESSION[‘akey’] = $row[‘akey’];
}else{
session_destroy();
session_start();
loginform();
exit;
}
?>
<?
function loginform(){
global $sitename;
?>
<CENTER>
<BR>
<FONT FACE=verdana color=#FFFFFF size=5><B><?=$sitename?> Login</B></FONT>
<BR>
<TABLE cellspacing=0>
<FORM method=post>
<TR><TD>Username:
<TD><INPUT type=text name=auid size=16>
<TR><TD>Pass:
<TD><INPUT type=pass name=pwd size=16>
<TR><TD colspan=2><INPUT type=submit value=”Log In >>”></TD>
</FORM>
</TABLE>
<BR>
<?
}
?>
This file is used to let your affiliates log in to the site.
Next, we create a file called “account.php”:
<?
include(“common.php”);
include(“login.php”);
include(“header.php”);
$mode = isset($_REQUEST[‘mode’]) ? $_REQUEST[‘mode’] : ”;
?>
<p>Welcome <?=$_SESSION[‘username’]?><br/>
<hr/>
<a href=’account.php’>Your Account</a>
|
<a href=’account.php?mode=affedit’>Edit Your Profile</a>
<br/>
<hr/></p>
<?
if($mode == ‘affedit’){
include(“affedit.php”);
}else{
include(“affstats.php”);
}
include(“footer.php”);
?>
This is the main file that your affiliates will use for viewing their stats and updating their account.
We now will create two more files as part of this section.
The first one is “affstats.php.” This file displays the affiliate stats.
<p>Your Affiliate Link:<br/>
<textarea rows=3 cols=50 wrap=SOFT><?=$siteurl?>/click.php?refid=<?=$_SESSION[‘akey’]?></textarea>
<hr/>
<?
$mystats = AffiliateStats($_SESSION[‘userId’]);
if($mystats[‘clicks’] > 0){
$ratio = number_format( (($mystats[‘orders’] / $mystats[‘clicks’]) * 100), 2 ).” %”;
}else{
$ratio = “0 %”;
}
?>
<b>Your Stats</b><br/></p>
<table align=center width=350 border=1>
<tr>
<td>Total Clicks:</td>
<td><?=number_format($mystats[‘clicks’],0)?> </td>
</tr>
<tr>
<td>Total Orders:</td>
<td><?=number_format($mystats[‘orders’],0)?> </td>
</tr>
<tr>
<td>Ratio %:</td>
<td><?=$ratio?> </td>
</tr>
</table>
<br>
<table align=center width=350 border=1>
<tr>
<td>Amount Earned:</td>
<td>$<?=number_format($mystats[‘order_total’],2)?></td>
</tr>
<tr>
<td>Payments Pending:</td>
<td>$<?=number_format($mystats[‘pending_total’],2)?></td>
</tr>
<tr>
<td>Amount Paid:</td>
<td>$<?=number_format($mystats[‘paid_total’],2)?></td>
</tr>
</table>
<hr/>
Next, create a file called “affedit.php.” This file lets your affiliates edit their accounts.
<?
$step = isset($_POST[‘step’]) ? $_POST[‘step’] : 1;
switch ($step){
case ‘2’:
AffiliateUpdate($_SESSION[‘userId’]);
echo “Your account has been updated<br>”;
break;
default:
$affinfo = mysql_fetch_assoc( mysql_query(“SELECT * FROM affiliates WHERE ID='{$_SESSION[‘userId’]}'”) );
?>
<table>
<form method=”POST” action=”account.php?mode=affedit”>
<input type=’hidden’ name=’step’ value=’2’>
<?
foreach($affiliateForm as $field=>$info){
echo “<tr>”;
if( !empty($info[0]) ){
echo “<td valign=top>{$info[0]}</td>”;
}
echo “<td valign=top>”;
switch($info[1]){
case “hidden”:
// hidden variables don’t get edited.
break;
case “password”:
echo “<input type=’password’ name='{$field}’ value=’$affinfo[$field]’>”;
break;
case “text”:
echo “<input type=’text’ name='{$field}’ value='{$affinfo[$field]}’>”;
break;
case “textarea”:
echo “<textarea name='{$field}’ rows=5 cols=25>{$affinfo[$field]}</textarea>”;
break;
case “select”:
$info[2] = explode(“|”,$info[2]);
echo “<select name='{$field}’>”;
foreach($info[2] as $k=>$v){
if($v == $affinfo[$field]){
echo “<option SELECTED>{$v}</option>”;
}else{
echo “<option>{$v}</option>”;
}
}
echo “</select>”;
break;
}
echo “</td>”;
echo “</tr>”;
}
?>
<tr>
<td colspan=2><input type=’submit’ value=’Save’></td>
</tr>
</form>
</table>
<?
break;
}
?>
Okay, this is it for the part of the script that handles affiliates managing their accounts. Now, we need to a way to manage affiliates.
{mospagebreak title=Adding Affiliate Code to Your Site}
There are two types of codes we have to set up for tracking affiliates.
The first is called “click.php.” This file is called when visitors click a link on an affiliate’s website.
<?
include(“common.php”);
if( $_REQUEST[‘refid’] ){
$mykey = makeKey(10);
SetCookie (“affid”,$mykey);
$_SESSION[‘affid’] = $mykey;
mysql_query(“UPDATE affiliates SET
clicks=clicks+1 WHERE akey='”.$_REQUEST[‘refid’].”‘”,DBH);
mysql_query(“INSERT INTO affstats SET
akey='”.$_REQUEST[‘refid’].”‘,type=’1’,
OrderID='”.$mykey.”‘”,DBH);
}
header(“Location: $redirecturl”);
?>
Now, create a file called “sale.php”:
<?
include(“common.php”);
$affid = $_SESSION[‘affid’];
if (!$affid){
$affid = $HTTP_COOKIE_VARS[‘affid’];
}
if (!$affid){
exit;
}else{
mysql_query(“UPDATE affstats SET
type=’2′,price='{$affpay}’,paid=’0′ where OrderID = ‘{$affid}'”,DBH);
}
?>
The file sale.php is called by adding it to your site like this:
<img src=”/affiliates/sale.php”>
Managing Affiliates
Managing affiliates is important. We need to have a way to manage affiliates so we can set them as being paid, and perform other tasks. For the Affiliate management part, we will create five files.
The first file is “accesscontrol.php.” This file is similiar to login.php but works for the management area.
<?php
session_start();
$auid = isset($_POST[‘auid’]) ? $_POST[‘auid’] : $_SESSION[‘auid’];
$pwd = isset($_POST[‘pwd’]) ? $_POST[‘pwd’] :
$_SESSION[‘pwd’];
if(!isset($auid)) {
loginform();
exit;
}
if( $auid != $aduser || $pwd != $adpass ){
unset($_SESSION[‘auid’]);
unset($_SESSION[‘pwd’]);
loginform();
exit;
}else if( $auid == $aduser && $pwd ==
$adpass ){
$_SESSION[‘auid’] = $auid;
$_SESSION[‘pwd’] = $pwd;
}
?>
<?
function loginform(){
global $sitename;
?>
<CENTER>
<BR>
<FONT FACE=verdana color=#FFFFFF
size=5><B><?=$sitename?> Login</B></FONT>
<BR>
<TABLE cellspacing=0>
<FORM method=post>
<TR><TD>Username:
<TD><INPUT type=text name=auid size=16>
<TR><TD>Pass:
<TD><INPUT type=pass name=pwd size=16>
<TR><TD colspan=2><INPUT type=submit
value=”Log In >>”></TD>
</FORM>
</TABLE>
<BR>
<?
}
?>
Now, we create “manage.php”:
<?
include(“common.php”);
include(“accesscontrol.php”);
include(“header.php”);
$mode = isset($_REQUEST[‘mode’]) ? $_REQUEST
[‘mode’] : ”;
switch($mode){
case “stats”:
include(“managestats.php”);
break;
case “payout”:
include(“payout.php”);
break;
default:
include(“managemain.php”);
break;
}
?>
<?
include(“footer.php”);
?>
Next, we do “managemain.php”:
<p>Here are your affiliates, click on a username to view their stats.</p>
<table border=1>
<tr>
<td>Username</td>
<td>Password</td>
<td>Email</td>
<td>Affiliate Key</td>
<?
$results = mysql_query(“SELECT * FROM
affiliates ORDER BY username;”,DBH);
while( $row = mysql_fetch_assoc($results) ){
echo “<tr>”;
echo “<td><a href=’manage.php?mode=stats&userId={$row[‘ID’]}’>{$row
[‘username’]}</a></td>”;
echo “<td>{$row[‘password’]}</td>”;
echo “<td>{$row[’email’]}</td>”;
echo “<td>{$row[‘akey’]}</td>”;
echo “</tr>”;
}
?>
</table>
The next file will be “managestats.php”:
<?
$mystats = AffiliateStats($_GET[‘userId’]);
if($mystats[‘clicks’] > 0){
$ratio = number_format( (($mystats
[‘orders’] / $mystats[‘clicks’]) * 100), 2 ).” %”;
}else{
$ratio = “0 %”;
}
?>
<b>Stats</b><br/>
<table align=center width=350 border=1>
<tr>
<td>Total Clicks:</td>
<td><?=number_format($mystats[‘clicks’],0)?> </td>
</tr>
<tr>
<td>Total Orders:</td>
<td><?=number_format($mystats[‘orders’],0)?> </td>
</tr>
<tr>
<td>Ratio %:</td>
<td><?=$ratio?> </td>
</tr>
</table>
<br>
<table align=center width=350 border=1>
<tr>
<td>Amount Earned:</td>
<td>$<?=number_format($mystats[‘order_total’],2)?></td>
</tr>
<tr>
<td>Payments Pending:</td>
<td>$<?=number_format($mystats[‘pending_total’],2)?></td>
</tr>
<tr>
<td>Amount Paid:</td>
<td>$<?=number_format($mystats[‘paid_total’],2)?></td>
</tr>
</table>
<?
echo “<div align=’center’><a
href=’manage.php?mode=payout&userId={$_GET
[‘userId’]}’>Manage Payments</a></div>”;
?>
And finally, we create “payout.php”:
<?
if($_GET[‘setaspaid’]){
mysql_query(“UPDATE affstats SET paid=’1′
WHERE ID='{$_GET[‘setaspaid’]}'”,DBH);
}
if($_GET[‘setasunpaid’]){
mysql_query(“UPDATE affstats SET paid=’0′
WHERE ID='{$_GET[‘setasunpaid’]}'”);
}
$sql = “SELECT * FROM affiliates WHERE
ID='{$_GET[‘userId’]}'”;
$lo = mysql_query( $sql );
$affi = mysql_fetch_assoc($lo);
?>
<? if($affi[‘ptype’]){ ?>
Requested Payment Method: <?=( strtolower($affi
[‘ptype’]) == “paypal” ? “PayPal” : “Check”)?
><br>
<? } ?>
Payment Account: <?=( strtolower($affi
[‘ptype’]) == “paypal” ? $affi[‘pemail’] :
nl2br($affi[‘address’]) )?><br>
<br>
<table align=center width=350 border=1>
<?
$q1 = “select * from affstats WHERE akey=
‘{$affi[‘akey’]}’ AND type=2 GROUP BY OrderID”;
$r1 = mysql_query($q1,DBH) or die(mysql_error());
while($a1 = mysql_fetch_assoc($r1)){
?>
<tr>
<td>Amount Earned:</td>
<td>$<?=$a1[‘price’]?></td>
<? if($a1[paid] == 1){ ?>
<td><a href=”manage.php?
mode=payout&userId=<?=$_GET[‘userId’]?
>&setasunpaid=<?=$a1[‘ID’]?>”>Set As Not
Paid</a></td>
<? }else{ ?>
<td><a href=”manage.php?
mode=payout&userId=<?=$_GET[‘userId’]?
>&setaspaid=<?=$a1[‘ID’]?>”>Set As
Paid</a></td>
<? } ?>
</tr>
<?
}
?>
</table>
