HomeMySQL Page 2 - Building a Simple Affiliate System in PHP/MySQL
Building The Database - MySQL
Setting up an affiliate system on your website can be very lucrative. While there are a lot of details involved, it need not be overly complicated. This article walks you through the steps of creating and setting up a basic affiliate system.
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.
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