4 Steps Simple Referral System With PHP MySQL (Free Source Code)

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.

A simple referral system in PHP and MySQL only has a few key components:

  1. Database tables to store referral codes and commissions owed.
  2. A PHP library to manage the referral codes and sales commissions.
  3. A sales page that will register affiliate codes, calculate commissions upon checkout.

But just how is this done? Let us walk through an example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Referral System Useful Bits & Links
What’s Next?

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a database and import the 1-database.sql file.
  • Change the database settings in 2-referral.php to your own.
  • Launch 3-sales.php?ref=johndoe in your browser and click the “buy now” button.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

PHP REFERRAL SYSTEM

All right, let us now get into the details of constructing a referral system with PHP and MYSQL.

 

STEP 1A) AFFILIATES DATABASE TABLE

1-database.sql
-- (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=latin1;

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 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.

 

 

STEP 1B) COMMISSIONS DATABASE TABLE

1-database.sql
-- (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` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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 orders 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

2-referral.php
<?php
class Referral {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo;
  private $stmt;
  public $error;
  function __construct () {
    try {
      $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
        ]
      );
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    $this->pdo = null;
    $this->stmt = null;
  }

  // (C) 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 () {
    // (C1) 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"])) {
      // (C2) CHECK IF VALID AFFILIATE MEMBER
      $this->stmt = $this->pdo->prepare("SELECT * FROM `affiliates` WHERE `ref_code`=?");
      $this->stmt->execute([$_GET["ref"]]);
      $aff = $this->stmt->fetch();

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

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

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

      // (D2) REGISTER COMMISSIONS
      try {
        $this->stmt = $this->pdo->prepare(
          "INSERT INTO `commissions` (`ref_code`, `comm_amount`, `order_id`) VALUES (?,?,?)"
        );
        $this->stmt->execute([$_SESSION["referral"]["c"], $amt, $oid]);
      } catch (Exception $ex) {
        $this->error = $ex->getMessage();
        return false;
      }
      
      // (D3) UP TO YOU - KEEP REFERRAL CODE AFTER SALES?
      unset($_SESSION["referral"]);
      return true;
    }
  }
}

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

// (F) 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 – On creating a $REF = new Referral() object, the constructor will automatically connect to the database. The destructor will close the connection when done.
  • C – 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/3-sales.php?ref=johndoe.
    • The referral code will be registered into $_SESSION["referral"] = ["c"=>REFERRAL CODE, "T"=>TIMESTAMP].
  • D – commission() is “part 2” of the process, run after the sale is complete. Captain Obvious, register and save the commission amount.
  • E & F – Database settings and creating $REF = new Referral() object.

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

 

 

STEP 3) DUMMY SALES PAGE

3-sales.php
<?php
// (A) REGISTER REFERRAL CODE (IF ANY)
require "2-referral.php";
$REF->set();
// print_r($_SESSSION);

// (B) HTML ?>
<h1>THE AWESOME SALES PAGE</h1>
<p>Sales Pitch Here</p>
<form method="post" action="4-checkout.php">
  <input type="submit" value="BUY NOW!"/>
</form>

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

 

STEP 4) CHECKOUT PAGE

4-checkout.php
<?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-referral.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.

 

 

USEFUL BITS & LINKS

That’s all for the guide, and here are some extras that may be useful to you.

 

BAREBONES SYSTEM ONLY!

As you can see, this is pretty much a barebones system – It works, but I will not recommend using it “as-it-is”. It is missing an affiliate portal, an admin panel, management, reports, and so much more. I shall leave a couple of links below that might help.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

7 thoughts on “4 Steps Simple Referral System With PHP MySQL (Free Source Code)”

  1. Nice one bro but can you work on link generator for a video that is uploaded in a page. For instance a video is been uploaded and the blogger wantbto generate a link for the video so that when any user clicks on the link he or she will be redirected to the blogger website and a new page will be displayed to him or her with a download button where he or she can download the video .

Leave a Comment

Your email address will not be published. Required fields are marked *