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