user-based referral link system with php + mysql

Micah Ketcham 0
Viewed 28

I'm attempting to create a referral system on my website which combines a user's IP address, username, and user agent all combined and then hashed to md5 to create a unique ID (shortened to 12 characters) for every user. The plan is to use a MySQL database to keep track of how many referrals (unique clicks on their referral link) each user has without using cookies or account creation. I have no formal education in PHP or MySQL so I've been trying to piece this together through searching online but I've found myself stuck in a tough spot.

I have successfully created a unique 12 character identifier for each user, and I've figured out how to pull the 12 character identifier from the referrer-URL. I can't figure out how to record and organize the user's unique referral id and referral amount while also crediting the referrer with 1 referral per unique ID (starting at 0). any advice would be much appreciated! thank you.

<!DOCTYPE html>
<html>
<body>

<?php
$ip = $_SERVER["REMOTE_ADDR"];
$user = $_SERVER["REMOTE_USER"];
$agent = $_SERVER["HTTP_USER_AGENT"];
$unique_id = substr(md5($ip . $user . $agent), 0, 12);
$full_ref_uri = $_SERVER["REQUEST_URI"];
$ref_uri = ltrim($full_ref_uri, "/?");

$servername = "myserver.com";
$username = "myusername";
$password = "mypassword";
$dbname = "mydb";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//this displays the referrer unique_id
echo $ref_uri;
echo "<br>";

//this records the user's unique id in the sql database
$sql = "INSERT INTO id (unique_id)
VALUES ('$unique_id')";

//this displays the user's generated referral link using their unique_id
if ($conn->query($sql) === true) {
    echo "http://my.website.com/?";
    echo $unique_id;
} else {
    echo "http://my.website.com/?";
    echo $unique_id;
    echo " already exists.";
}

$conn->close();
?>

</body>
</html>
mysql php
1 Answers

I can't figure out how to record and organize the user's unique referral id and referral amount while also crediting the referrer with 1 referral per unique ID

NOTE: You have not given us any indication as to how your data in your database looks or how your table(s) are constructed.

Your database should look something like this:

id user_id ref_code url_string visits
1 3 abcdef123 https://stack.com 2
2 4 brgerd123 https://stonks.com 0

So when the user clicks on your PHP link e.g https://mywebsite.com/link/abcdef123 then that PHP page (e.g mywebsite.com/link.php) (using mod_rewrite or similar) then does the following things:

  1. loads the given variable from the visitor.
  2. Checks the database for said variable under reference column .
  3. Retrieves the unique id and destination URL of the row with that variable.
  4. Updates the visits counter by +1 .
  5. Redirects the user to that destination URL.
  6. Catches if the URL is malformed or invalid or if bad reference is given and redirects user to a (catch-all) error page.

One at a time, using the Mod_rewrite question linked above we can have:

$link = $_GET['link'];
$link = preg_replace('~[^a-z0-9]~','', $link); // Clean the link given so it is safe.
if(!empty($link)){
    $finderSQL = "SELECT id, url_string FROM <table> WHERE ref_code = '$link' LIMIT 1"
    $finder = $conn->query($finderSQL);
}

The above checks the incoming reference is valid and retrieves the URL and the primary Id. It's been years since I've used this method of MySQL and I would highly HIGHLY recommend you explore Using Prepared Statements.

Once the $finder varaible is populated with the id and URL link, then you can update the id with:

$conn->query("UPDATE <table> SET visits = visits + 1 WHERE id = " .$finder['id']. ");

And you can finalise the script by checking the URL is valid then telling the browser to visit the endpoint destination:

if(filter_var($finder['url_string'], FILTER_VALIDATE_URL) !== false){
    header("Location: ".$finder['url_string']);
    exit;
}

You should be using prepared statements and mod_rewrites as well as if loops as exampled above to catch if a reference code or an end point url are invalid etc.

You should set your id column on MySQL to be a UNIQUE indexed column so you know each data is unique. You should also set the unique id as larger than 12 characters as that's too small to be inherently unique.

MD5 is extremely limiting in what you're doing and if the code is not hidden, which it doesn't seem to be from your question, you can do better to generate a code from base64 or similar. IF you DO need to have a hash (ie hidden) source then you should be using password_hash with BCRYPT and you can simply snip of the first 7 characters of that to generate a unique link that can also be recostructed (append the same 7 characters [$2y$14$ for example] which are always the same) to reconstruct the original hash (to compare, if needed).

I think from your question then a base64 encoding should be absolutely fine, you can do something like bsse64 encode id + fixed value such as: id = 5, fixed value = "stackoverflow" and create NXN0YWNrb3ZlcmZsb3c= as your unique link reference. THen when that link is used, the PHP can base64_decode it, remove the text ("stackoverflow") and then be left with the id number in the DB to use to find the URL.

userGXPe0
Related