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

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!

ⓘ 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

QUICK NOTES

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

If you spot a bug, feel free to comment below. I try to answer short 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.

SCREENSHOT

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.

STEP 1) THE DATABASE

1A) AFFILIATES TABLE

1-database.sql

-- (A) AFFILIATES TABLECREATE 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`);

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 aref_code field to it.

1B) COMMISSIONS TABLE

1-database.sql

-- (B) COMMISSIONS TABLECREATE 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`);

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

2-lib.php

<?phpclass 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) { $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 OBJECTsession_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

3a-sales.php

<?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 - 100% discount. Buy now and get free this that everything!</p> <div class="note"><?phpif (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

3b-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 COMMISSIONrequire "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