Challenge: Making an Affiliate Registration with Hierarchy

This is the second post in the challenge series of posts. One of my friends was struggling to get an affiliate application completed. So this is the task. You register yourself, you sign into the application and then you get a unique registration affiliate code. Using that code, whoever registers, will become your sub-affiliates.

Thinking of the database, I would like to keep it simple (KISS). To start with, let me have only the following fields in the database:

• Username
• Password
• Full Name
• Email Address
• Parent (NULL for no parent)

Let's start with the simple CREATE SQL query for the table:

CREATE TABLE `users` (  
  `Username` varchar(75) NOT NULL PRIMARY KEY,
  `Password` varchar(75) NOT NULL,
  `Email` varchar(75) NOT NULL UNIQUE,
  `FullName` varchar(75) NOT NULL,
  `Parent` varchar(75) NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE='InnoDB';

We are making the Username the primary key and also Email unique, so that people cannot register more than once. To keep things simple, I am going to use BootFlat Admin by Silverbux and crack on with the UI stuff.

Since I like keeping things simpler, I choose to have one single file for doing the authentication and user management of the whole system. Let's call it system.php:

db.inc

<?php  
  session_start();
  $conn = mysqli_connect("localhost", "username", "password", "affiliate")
            or die("Cannot Connect to MySQL Server!");
  function insertRow ( $tableName, $data ) {
    // Inserts the data into a table.
  }
  function execSql ( $sql ) {
    // Executes the given SQL, else returns false.
  }
  function resSql ( $sql ) {
    // Returns a result set of the SQL, else returns false.
  }
  function SanitizeData ( $Data ) {
    // Sanitises the user inputs to avoid MySQL Injection.
  }
  function LastInsertId ( ) {
    // Returns the last inserted ID.
  }
?>

system.php

<?php  
  include "db.inc";
  if (!count($_POST) || !isset($_GET["action"]) || empty($_GET["action"])) {
    header('HTTP/1.0 403 Forbidden');
    exit;
  }
  switch ($_GET["action"]) {
    case "login":
      if (!empty($_POST["username"]) && !empty($_POST["password"])) {
        $username = SanitizeData($_POST["username"]);
        $password = SanitizeData($_POST["password"]);
        $resSql = resSql("SELECT * FROM `users` WHERE `Username`='{$username}' AND `Password`=PASSWORD('{$password}')");
        if ($resSql && mysqli_num_rows($resSql) == 1) {
          $_SESSION["user"] = mysqli_fetch_assoc($resSql);
          header("Location: ./");
          die();
        } else {
          session_destroy();
          header("Location: ./?error=wrong");
          die();
        }
      } else {
        header("Location: ./?error=incomplete");
        die();
      }
    case "register":
      if (!empty($_POST["Username"]) && !empty($_POST["Password"]) && !empty($_POST["FullName"]) && !empty($_POST["Email"])) {
        $data = array();
        $data["Username"] = $_POST["Username"] = SanitizeData($_POST["Username"]);
        $data["Password"] = $_POST["Password"] = "PASSWORD('" . SanitizeData($_POST["Password"]) . "')";
        $data["Email"]    = $_POST["Email"]    = SanitizeData($_POST["Email"]);
        $data["FullName"] = $_POST["FullName"] = SanitizeData($_POST["FullName"]);
        if (isset($_SESSION["affiliate"]))
          $data["Parent"] = $_SESSION["affiliate"];
        $newRecord = insertRow("users", $data);
      } else {
        header("Location: ./?error=regerror");
        die();
      }
    case "logout":
      session_destroy();
      header("Location: ./");
      die();
  }
?>

The logic is very simple. In the system.php, I am including the database.inc, which has three uses:

• Start the session.
• Connect to the Database.
• Functions to help with Database Management.

The next is a check inside the system.php, to make sure that the request is both POST request and it contains the action parameter as well. The simple switch statement does the job of checking the action and based on that, the data is inserted or the session is set up. Main thing it does is, setting $_SESSION variable and mysqli_query("INSERT ...").

Before that, what about the affiliates? So let's go ahead and construct the front end of this system and let's come back here again. I am starting with this simple looking HTML structure:

<!DOCTYPE html>  
<html lang="en">  
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width" />
    <link rel="stylesheet" href="css/site.min.css" />
    <script src="js/site.min.js"></script>
    <title>Affiliate</title>
  </head>
  <body>
    <!-- Content -->
  </body>
</html>  

Note: I am keeping this UI as minimal as possible and not using any custom CSS for this. For more information, please refer to Bootstrap Documentation.

The next step is to add the sign up and sign in forms, which looks similar to the following:

And then once you sign up and sign in to the application, you will see the following screen.

The URL that's shown in the image is:

http://praveen.dev/affiliates/affiliate.php?affiliate=Praveen

The URL that is generated is using the following code:

(isset($_SERVER['HTTPS']) ? "https" : "http") . "://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]affiliate.php?affiliate={$_SESSION["user"]["Username"]}";

The above URL has several parts. The parts are as follows:

• (isset($_SERVER['HTTPS']) ? "https" : "http")
  This part detects if the server is running on HTTP or SSL.
• $_SERVER[HTTP_HOST]
  This gets the current domain name of the requested URL.
• $_SERVER[REQUEST_URI]
  This gets the requested path after the domain name.
• affiliate.php?affiliate=
  We need to pass the username to the affiliate.php file as a parameter affiliate.
• {$_SESSION["user"]["Username"]}
  Gets the current username from the session.

Now coming to the affiliate.php, the content is very simple:

<?php  
  include "db.inc";
  if (resSql("SELECT * FROM `users` WHERE `Username`='" . SanitizeData($_GET["affiliate"]) . "'")) {
    $_SESSION["affiliate"] = SanitizeData($_GET["affiliate"]);
  }
  header("Location: ./");
?>

The following happens in the affiliate.php with the above code:

1. Include the database connection and start the session.
2. Checks the sanitised input with the content of the database. This is a sanity check to make sure non-existent users are not added to the database.
3. Session is set if the referred affiliate is found.
4. Send the user back to the registration / login screen.

Caveat: If the user signs in with an existing username and password, our current logic will remove the affiliate referral. This seems to be kind of okay, as we are targeting only the new users and not the existing ones.

During the registration function, we check if there's any trace of referral or affiliate username exists in the session by checking in the system.php in the registration phase:

if (isset($_SESSION["affiliate"]))  
  $data["Parent"] = $_SESSION["affiliate"];

Along with the registration data, the Parent value will also be set and sent to the database. If this entry is not available in the session data, then NULL will be stored in the database.

This way, if I use my affiliate ID and create another user, you can see the difference between a parent user and the child one:

+----------+-------------------------------------------+-------------------------+---------------+---------+---------------------+
| Username |                 Password                  |          Email          |   FullName    | Parent  |      TimeStamp      |
+----------+-------------------------------------------+-------------------------+---------------+---------+---------------------+
| Praveen  | *80A1F5FE6F1CEEEA333D69B624DCD5237439E861 | [email protected] | Praveen Kumar | NULL    | 2017-05-22 15:30:50 |
| Child    | *80A1F5FE6F1CEEEA333D69B624DCD5237439E861 | [email protected]   | Child Kumar   | Praveen | 2017-05-22 18:00:25 |
+----------+-------------------------------------------+-------------------------+---------------+---------+---------------------+

The record that doesn't have any parent will have its Parent field stored as NULL, while those that have their affiliates will have their usernames stored in the Parent column.

To list out all the affiliates of a currently logged in user, we need to run a simple SELECT query with some extra WHERE parameters that help in getting those registered under the currently logged in user.

SELECT * FROM `users` WHERE `Parent`='{$_SESSION["user"]["Username"]}'  

Here, $_SESSION["user"]["Username"] stores the currently logged in user's username. Looping through the results, we can show the list. We can also implement an empty state. Have a look at both the states below:

The code for the above is very simple. We are displaying everything inside a Bootstrap List Group:

<ul class="list-group">  
<?php  
  $affSql = resSql("SELECT * FROM `users` WHERE `Parent`='{$_SESSION["user"]["Username"]}'");
  if ($affSql && mysqli_num_rows($affSql))
    while (false != ($affData = mysqli_fetch_assoc($affSql)))
      echo '<li class="list-group-item">' . $affData["FullName"] . ' (' . $affData["Username"] . ')</li>';
  else
    echo '<li class="list-group-item list-group-item-danger text-center">No affiliates found.</li>';
?>
</ul>  

The whole program has been completed without much styles, but having a great look. In the next challenge, we'll see how to show the hierarchy of your affiliates.

As usual, if you have any comments, please do use the below form. If you have a better idea, please feel free to let me know. Do share it with your friends, colleagues and network. Until next time then.