3 Steps Simple Referral System In PHP MySQL (Free Download)

Welcome to a quick tutorial on how to create a simple referral system with PHP and MySQL. So you have a website that sells some products and you are looking to boost sales – Affiliate sales may be able to help. But just how is this done? Let us walk through an example in this guide, read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

 

STEP 1) THE DATABASE

1A) AFFILIATES TABLE

-- (A) AFFILIATES TABLE
CREATE TABLE `affiliates` (
  `ref_code` varchar(32) NOT NULL,
  `aff_email` varchar(255) NOT NULL,
  `aff_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `affiliates`
  ADD PRIMARY KEY (`ref_code`),
  ADD UNIQUE KEY `aff_email` (`aff_email`),
  ADD KEY `aff_name` (`aff_name`);

Field	Description
ref_code	The referral code, primary key. Everyone has a different requirement, so I leave it up to you – Randomly generate one, or manually assign a “nice agent code” to all the partners.
aff_email	The affiliate’s email. Unique field to prevent multiple registrations.
aff_name	The affiliate’s name.

First, this is a simple table to store the affiliate information. Feel free to modify it to your own project needs, for example, add a password field if you have a login portal for partners.

P.S. If you already have an existing users table, you can just add a ref_code field to it.

 

 

1B) COMMISSIONS TABLE

-- (B) COMMISSIONS TABLE
CREATE TABLE `commissions` (
  `ref_code` varchar(32) NOT NULL,
  `comm_date` datetime NOT NULL DEFAULT current_timestamp(),
  `comm_amount` decimal(10,2) NOT NULL,
  `order_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `commissions`
  ADD PRIMARY KEY (`ref_code`,`comm_date`);

Field	Description
ref_code	Partial primary key, the referral code – Who made the sales.
comm_date	Partial primary key, commission date.
comm_amount	Commission amount.
order_id	Foreign key. Links back to your “main orders table”.

That’s right, you should already have your own payment and order system in place. This is just a “supplement table” to contain information on the commissions and feel free to add more fields as required. For example, notes, commission percentage, etc…

 

 

STEP 2) PHP REFERRAL LIBRARY

<?php
class Referral {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo;
  private $stmt;
  public $error;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    $this->pdo = null;
    $this->stmt = null;
  }
 
  // (C) HELPER - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) REGISTER REFERRAL CODE - FIRST COME FIRST SERVE
  // I.E. CUSTOMER ACCESS YOUR SITE WITH 2 REFERRAL CODES.
  // THE FIRST REFERRAL CODE WILL BE VALID FOR 24 HRS.
  // THE SECOND REFERRAL CODE WILL NOT OVERRIDE THE FIRST
  function set () {
    // (D1) CHECK IF EXISTING REFERRAL CODE HAS EXPIRED
    if (isset($_SESSION["referral"])) {
      if (strtotime("now") >= ($_SESSION["referral"]["t"] + REF_VALID)) {
        unset($_SESSION["referral"]);
      }
    }

    if (!isset($_SESSION["referral"]) && isset($_GET["ref"])) {
      // (D2) CHECK IF VALID AFFILIATE MEMBER
      $this->query("SELECT * FROM `affiliates` WHERE `ref_code`=?", [$_GET["ref"]]);
      $aff = $this->stmt->fetch();

      // (D3) REGISTER INTO SESSION IF VALID
      if (is_array($aff)) {
        $_SESSION["referral"] = [
          "c" => $aff["ref_code"],
          "t" => strtotime("now")
        ];
      }

      // (D4) INVALID REFERRAL CODE
      else {
        $this->error = "Invalid referral code";
        return false;
      }
    }
    return true;
  }

  // (E) REGISTER SALES COMMISSION
  function commission ($oid, $amt) {
    if (isset($_SESSION["referral"])) {
      // (E1) CHECK IF EXISTING REFERRAL CODE EXPIRED
      if (strtotime("now") >= ($_SESSION["referral"]["t"] + REF_VALID)) {
        unset($_SESSION["referral"]);
        $this->error = "Referral code expired";
        return false;
      }

      // (E2) REGISTER COMMISSIONS
      $this->query(
        "INSERT INTO `commissions` (`ref_code`, `comm_amount`, `order_id`) VALUES (?,?,?)",
        [$_SESSION["referral"]["c"], $amt, $oid]
      );
      
      // (E3) UP TO YOU - KEEP REFERRAL CODE AFTER SALES?
      unset($_SESSION["referral"]);
      return true;
    }
  }
}

// (F) SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("REF_VALID", 86400); // 24 hours = 86400 secs

// (G) START SEESSION + CREATE NEW REFERRAL OBJECT
session_start();
$REF = new Referral();

This may seem complicated at first, but keep calm and look carefully.

• (A, B, G) On creating $REF = new Referral(), the constructor automatically connects to the database. The destructor closes the connection.
• (C) query() A helper function to run an SQL query.
• (D) set() Is “part 1” of the process.
  • Run this on the sales page to register the $_GET["ref"] referral code into the session. For example, https://site.com/3a-sales.php?ref=jondoe.
  • The referral code will be registered into $_SESSION["referral"] = ["c"=>REFERRAL CODE, "T"=>TIMESTAMP].
• (E) commission() Is “part 2” of the process, run after the sale is complete. Captain Obvious, register and save the commission amount.
• (F) Remember to change the database settings to your own.

That’s all, but you might need to change the functions to fit the policies of your own project.

 

 

STEP 3) DUMMY SALES PAGE

3A) SALES PITCH PAGE

<?php
// (A) REGISTER REFERRAL CODE (IF ANY)
require "2-lib.php";
$REF->set();

// (B) HTML SALES PAGE ?>
<img src="black.png">
<p>Sales pitch here - Buy now and get this that everything!</p>
 
<div class="note"><?php
if (isset($_SESSION["referral"])) {
  echo "Current referral - ";
  print_r($_SESSION["referral"]);
} else {
  echo "No referral set - Please access this page with 3a-sales.php?ref=jondoe";
}
?></div>
 
<form method="post" action="3b-checkout.php">
  <input type="submit" value="BUY NOW!">
</form>

Yep, it’s that simple. Access http://site.com/3a-sales.php?ref=johndoe and that will register $_SESSION["referral"] = ["c"=>"jondoe", "t"=>TIME NOW].

 

3B) CHECKOUT

<?php
// (A) DO YOUR PAYMENT & ORDER PROCESSING
// LET'S SAY PAYMENT + CHECKOUT OK - ORDER ID 999, COMMISSION AMOUNT OWED IS $87.65
$orderID = 999;
$commission = 87.65;
 
// (B) REGISTER COMMISSION
require "2-lib.php";
$pass = $REF->commission($orderID, $commission);
echo $pass ? "ok" : $REF->error ;

After the “normal checkout”, simply pop the order ID and commission amount into $REF->commission() to register it.

 

 

 

IMPROVEMENT IDEAS

As you can see, this is pretty much a barebones system – It works, but it is far from a “full-fledged professional affiliate system”. Plenty of stuff needs to be done:

• Do you want to open up for “public affiliate signup”?
• Create an affiliate portal to view/download sales reports? Auto periodic reports? Affiliate newsletters?
• Complete your own “affiliate rules” – How long do the affiliate links last? First come first serve? Or allow “override”?
• Complete your own checkout and commission calculations – Fixed or percentage? Payment processors. Instant commission payments or monthly?
• An admin panel to manage affiliates, reports, sales, commissions, and so much more.
• If you don’t already have a “shopping cart” – I shall leave a couple of links below that may help.

 

LINKS & REFERENCES

• How To Create A Simple Shopping Cart With PHP MySQL – Code Boxx
• Simple eCommerce Website (No Database) – Code Boxx
• User Registration In PHP MYSQL – Code Boxx
• User Login With PHP MySQL – Code Boxx
• Simple Admin Panel – Code Boxx