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.



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

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


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


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

(Video) How To Make A PHP Referral System Script - Referral URL


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.


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

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




-- (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`);
ref_codeThe 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_emailThe affiliate’s email. Unique field to prevent multiple registrations.
aff_nameThe 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 aref_code field to it.



(Video) PHP Referral System Extra Tiers - Downline

-- (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`);
ref_codePartial primary key, the referral code – Who made the sales.
comm_datePartial primary key, commission date.
comm_amountCommission amount.
order_idForeign 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…



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




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



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

(Video) Make a User Referral Website Using Php & MySql - in Hindi 2021


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

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


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.


  • 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


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

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!


1. Simple Membership System With PHP MYSQL
(Code Boxx)
2. Advance Bitcoin Mining System using PHP MySQL | Free Source Code Download | Final Year Project
3. Simple Referral System With Laravel
(W3 Coders)
4. User Registered with Referral Code or Without Referral Code in Laravel - Referral System Project #1
(Programming Experience)
5. How to create affiliate system in php || Affiliate system php script
6. How to Create Referral System with Mysql database in Kodular || Bangla & Hindi
(Programmer Shonku)
Top Articles
Latest Posts
Article information

Author: Ms. Lucile Johns

Last Updated: 2023/06/11

Views: 6184

Rating: 4 / 5 (61 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Ms. Lucile Johns

Birthday: 1999-11-16

Address: Suite 237 56046 Walsh Coves, West Enid, VT 46557

Phone: +59115435987187

Job: Education Supervisor

Hobby: Genealogy, Stone skipping, Skydiving, Nordic skating, Couponing, Coloring, Gardening

Introduction: My name is Ms. Lucile Johns, I am a successful, friendly, friendly, homely, adventurous, handsome, delightful person who loves writing and wants to share my knowledge and understanding with you.