ASSUMPTIONS & NOTES

Most of you guys here should already have an existing website that is selling some stuff, and just want a referral system “extension”. With that, we shall not reinvent the wheel and create yet another shopping cart nor users system nor login system.

Also, this guide will only touch on the board mechanics. We shall not go into the details for “how to implement for Shopify, Magento, Drupal, WordPress, whatever else is out there” – Or this guide will never end… More importantly, no free lunch will be given to those who are working on paid projects. You have to earn your own pay cheque.

SYSTEM OVERVIEW

This will be the process flow of the entire system in general.

• Referrals will sign up for your program.
• The system will generate unique referral links, and your referrals will proceed to promote using these links.
• Customers will make purchases using those links.
• We credit the sales to the respective referrals.
• Finally, we can generate sales reports and pay the commissions accordingly.

But as with the notes above, everyone will have a different system and a different approval process. We will not go deep into referral signup nor recreate an entire shopping cart.

REFERRALS TABLE

First, we need to have a table to hold all the information of your referrals.

CREATE TABLE `referral` (
  `referral_id` int(11) NOT NULL,
  `referral_name` varchar(255) NOT NULL,
  `referral_email` varchar(255) NOT NULL,
  `referral_password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `referral`
  ADD PRIMARY KEY (`referral_id`),
  ADD UNIQUE KEY `referral_name` (`referral_name`),
  ADD UNIQUE KEY `referral_email` (`referral_email`);

ALTER TABLE `referral`
  MODIFY `referral_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Is this not a user table? Yes, sort of. You can actually modify your existing user table if you want, and just add another “user role” column to distinguish between the administrators, customers, and referrals. But for the sake of a “non-destructive” example, we will be using this table instead.

REFERRAL SALES TABLE

Next, we will need a table to hold all the referral sales.

CREATE TABLE `referral_sales` (
  `referral_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_amount` decimal(10,2) NOT NULL,
  `order_commission` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `referral_sales`
  ADD PRIMARY KEY (`referral_id`,`order_id`),
  ADD KEY `order_date` (`order_date`);
COMMIT;

Again, we could have just added a referral ID to your existing orders table. But to avoid messing with your existing project, we shall work on this separate sales table.

CONFIG FILE

The first script that we have is a config file, just a place to store all the settings and stuff. Please remember to change the database settings to your own.

DATABASE LIBRARY

pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
    }

    // ERROR - CRITICAL STOP - THROW ERROR MESSAGE
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct () {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function exec ($sql, $data=null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data

    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function start () {
  // start() : auto-commit off

    $this->pdo->beginTransaction();
  }

  function end ($commit=1) {
  // end() : commit or roll back?

    if ($commit) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
  }

  function fetchAll ($sql, $cond=null, $key=null, $value=null) {
  // fetchAll() : perform select query (multiple rows expected)
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided. If string provided, sort in $key=>$value order. If function provided, will be a custom sort.

    $result = [];
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      // Sort in given order
      if (isset($key)) {
        if (isset($value)) {
          if (is_callable($value)) {
            while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
              $result[$row[$key]] = $value($row);
            }
          } else {
            while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
              $result[$row[$key]] = $row[$value];
            }
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      }
      // No key-value sort order
      else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    // Return result
    $this->stmt = null;
    return count($result)==0 ? false : $result ;
  }

  function fetch ($sql, $cond=null, $sort=null) {
  // fetch() : perform select query (single row expected)
  //           returns an array of column => value
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $sort : custom sort function

    $result = [];
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (is_callable($sort)) {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result = $sort($row);
        }
      } else {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result = $row;
        }
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    // Return result
    $this->stmt = null;
    return count($result)==0 ? false : $result ;
  }

  function fetchCol ($sql, $cond=null) {
  // fetchCol() : yet another version of fetch that returns a flat array
  // I.E. Good for one column SELECT `col` FROM `table`

    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($cond);
    $result = $this->stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    return count($result)==0 ? false : $result;
  }
}

Next, we have a database library that will do all the SQL heavy lifting work. This is based on PDO, so it should work with a lot other databases than just MySQL.

STEP 1) CREATE REFERRAL ACCOUNTS

exec(
  "INSERT INTO `referral` (`referral_name`, `referral_email`, `referral_password`) VALUES (?, ?, ?)",
  ["johndoe", "[email protected]", "123456"]
);
echo $pass ? "OK" : $libDB->error;

The first step of your referral system is to create a referral sign-up page… Which could be anything from a simple HTML form, to a complex manual screening process. You might also want to create an entire referral login portal for your partners.

STEP 2) GENERATE REFERRAL LINKS

fetch(
  "SELECT * FROM `referral` WHERE `referral_id`=?",
  [$rID]
);

if (is_array($referral)) { 
  echo $url . "?ref=" . $referral['referral_name'];
} else {
  echo $url;
}

Next, we have to generate referral links for your partners to promote. This is as easy as pointing to a landing page with their referral login name placed in the query string – ?ref=REFERRAL NAME.

STEP 3) REFERRAL LINK LANDING PAGE

fetch(
    "SELECT * FROM `referral` WHERE `referral_name`=?",
    [$_GET['ref']]
  );
  if (is_array($referral)) {
    // We put the referral id into the session
    $_SESSION['ref'] = $referral['referral_id'];
  }
  // Redirect user to another page when done
  // This is to hide the referral link & stop users from messing with it
  header("Location: " . $redirect);
  die();
}
print_r($_SESSION);

So now, the customers will use the affiliate link and go to your specified landing page. There is not much trickery to this as well, just keep the referral ID in the session $_SESSION['ref'], so that we know this particular customer is introduced by that partner. Also, you might want to set some sort of timestamp or timeout – So that only orders within a set timeframe will be credited.

STEP 4) REFERRAL SALES

checkout() or something...
// Then calculate the own referral commission
$rID = $_SESSION['ref'];
$oID = 999;
$date = date("Y-m-d H:i:s");
$amount = 999.99;
$commission = 88.88;
$pass = $libDB->exec(
  "INSERT INTO `referral_sales` (`referral_id`, `order_id`, `order_date`, `order_amount`, `order_commission`) VALUES (?, ?, ?, ?, ?)",
  [$rID, $oID, $date, $amount, $commission]
);
echo $pass ? "OK" : $libDB->error;

// END THE REFERRAL SESSION IF YOU WANT
unset($_SESSION['ref']);

When the customer checks out the order, remember to give a commission to your partner. Pretty straightforward again – After processing the main order, proceed to calculate the commissions and put it into the referral sales table.

STEP 5) REPORTING

fetchAll(
  "SELECT * FROM `referral_sales` WHERE `referral_id`=? AND `order_date` BETWEEN ? AND ?",
  [$rid, $start, $end], "order_id"
);
print_r($sales);

Well, the so-called final step is to generate monthly sales reports and send them to your partners… Plus their commissions.

NEW PROJECT?

Are you starting from scratch!? Things are going to get a little rough, and you will need to create a shopping cart plus a user system. Here are 2 of my other guides that might be able to help you:

3 Steps Simple PHP MYSQL Shopping Cart (Free Source Code)

4 Steps Simple PHP User Registration Form (With Email Verification)

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

The post 5 Steps Simple PHP Referral System appeared first on Code Boxx.