Create affliate URL in PHP

I’m trying to create an affiliate url like http://example.com/ref/referrer.

I have 2 php files and a .htaccess file. I have a database table called ‘referral’ that stores username of users in a field called ‘referrer’.

index.php

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
$query = "SELECT referrer FROM referral";
$result = $conn->query($query);
$ref=mysqli_fetch_array($result);
$referrer=$ref['referrer'];

//Create referral URL
$refurl="http://example.com/ref/$referrer";
?>

welcome.php (landing page for those who came to site through ref link).

<?php
$referrer=mysqli_real_escape_string($con,$_GET["referrer"]);
$counted = "clicks"; 
//select user
$query="SELECT * FROM referral WHERE referrer='$referrer'";
$result=mysqli_query($con,$query) or die('error');
$user_ref=mysqli_fetch_array($result);
$user=$user_ref['user'];

if(!isset($_COOKIE[$counted])) { 

    //cookie for visitor
    setcookie("clicks", $counted, time() + (86400 * 7), "/refer/"); //7days

    //page view Counts
    $sql = "UPDATE referral SET clicks=clicks+1 WHERE referrer='$referrer'";
    $result_set=mysqli_query($con, $sql);
}
if($user_ref == false) {
    echo 'Sorry, the user does not exist.';
} else {  
    echo You were referred by $user;  
?>

.htaccess file

RewriteEngine On
RewriteRule ^([a-zA-Z0-9_-]+)$ welcome.php?referrer=$1

The problem is that the referral url does not work. When clicked, it’s supposed to load the welcome page, but it doesn’t. Please can someone tell me what I’m not doing right or missing?

Answer

With the suggestions of @RiggsFolly and @UTAlan, here is what I did to make it work, for the sake of others who may be in need of the same solution.

Run below query in your database

CREATE TABLE IF NOT EXISTS `referral` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(500) NOT NULL,
  `referrer` varchar(500) NOT NULL,
  `clicks` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`,`referrer`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

db.php

<?php
session_start();
error_reporting(0);
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'password';
$dbname = 'my_database';

$con=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) //connect to the database server
or die ("Could not connect to mysql because ".mysqli_error());
mysqli_select_db($con,$dbname) //select the database
or die ("Could not select to mysql because ".mysqli_error());
?>

index.php

<?php
include('db.php');
$conn = new mysqli($servername, $username, $password, $dbname);
$query = "SELECT referrer FROM referral";
$result = $conn->query($query);
$ref=mysqli_fetch_array($result);
$referrer=$ref['referrer'];

//Create referral URL
$refurl="http://example.com/ref/$referrer";
//redirect to welcome page
header("Location: $refurl");
}
?>

welcome.php //landing page for those who came to site via referral link

<?php
include('db.php');
$referrer=mysqli_real_escape_string($con,$_GET["referrer"]);
$counted = "clicks"; 
//select user
$query="SELECT * FROM referral WHERE referrer='$referrer'";
$result=mysqli_query($con,$query) or die('error');
$user_ref=mysqli_fetch_array($result);
$user=$user_ref['user'];

if(!isset($_COOKIE[$counted])) { 

    //cookie for visitor
    setcookie("clicks", $counted, time() + (86400 * 7), "/ref/"); //7days

    //page view Counts
    $sql = "UPDATE referral SET clicks=clicks+1 WHERE referrer='$referrer'";
    $result_set=mysqli_query($con, $sql);
}
if($user_ref == false) {
    echo 'Sorry, the user does not exist.';
} else {  
    echo 'You were referred by $user';  
?>

.htaccess file

RewriteEngine On
RewriteRule ^ref/([a-zA-Z0-9_-]+)$ welcome.php?referrer=$1 

Credit to Santhosh Veer of AllWebTuts (http://allwebtuts.com) for his insight.