PHP: Getting URL Referral Key

Question

I'm working on an email referal link for user registration with PHP.

The idea is that when you register, a random string is generated as a url referral and sent to your inbox. Then when you click the link in the email you get sent to a page that confirms your account.

However I've been running into problems getting the referral key from the URL and matching it with the DB.

So far this is what I have:

// example URL - http://example.come?ref=5493tgfd83

$passkey = $_GET['ref'];
$success = true;


$checkKey = "SELECT * FROM $tbl_name WHERE confirmCode ='$passkey'";
$confirmKey = mysql_query($checkKey);

if($confirmKey){
    $countKey = mysql_num_rows($confirmKey);
}

// key doesn't match redirect to 404
if($countKey != 1 ){
        header("Location: 404.php");
        $success = false;
}

At the moment, the query isn't running and the page isn't redirecting as I expect. i.e if I enter the wrong key, the page doesn't redirect.

[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Apr 29 '14 at 04:00
Set the following in your `php.ini` file and restart your web server - `error_reporting = E_ALL` and `display_errors = On`. Chances are you're hitting some undefined indexes and / or variables. — Phil, Apr 29 '14 at 04:03
did you try to step-by-step debug using `var_dump()` the following?. Example: before the line `if($countKey != 1){`, try to examine the earlier parts. — user1978142, Apr 29 '14 at 04:07
@Fred-ii- For starters, `$confirmKey` is a *mysql* result resource — Phil, Apr 29 '14 at 04:26

Funk Forty Niner · Answer 1 · 2014-04-29T14:58:41.103

Here are two working examples that will get you going.

You will get better results by using num_rows

Using mysqli_* functions with prepared statements:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_PASS = "xxx";
$DB_USER = "xxx";

$db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($db->connect_errno > 0) {
  die('Connection failed [' . $db->connect_error . ']');
}

// $passkey = $_GET['ref'];
$passkey = mysqli_real_escape_string($db,$_GET['ref']);

$tbl_name = "yourtable";

// $query = "SELECT * FROM $tbl_name WHERE confirmCode=?";
$query = "SELECT confirmCode FROM $tbl_name WHERE confirmCode=?";

if ($stmt = $db->prepare($query)){

        $stmt->bind_param("s", $passkey);

        if($stmt->execute()){

            $stmt->store_result();

            if ($stmt->num_rows == 1){

            echo "Code verified.";
            exit;

            }

else{ 
echo "Sorry.";
// uncomment below and delete the above echo
//    header("HTTP/1.1 404 Not Found");
//    header("Location: 404.php");
//    exit;
}
        }
    }

Using mysql_* functions:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$connect = mysql_connect("xxx","xxx","xxx") or die("Error Connecting To MYSQL Server");
mysql_select_db("xxx") or die("Error connecting to database");

// $passkey = $_GET['ref'];
$passkey = mysql_real_escape_string($_GET['ref']);

$tbl_name = "yourtable";

$checkKey = "SELECT * FROM $tbl_name WHERE confirmCode ='$passkey'";
$confirmKey = mysql_query($checkKey);

if (mysql_num_rows($confirmKey)) {
echo "Code verified.";
}

else{
echo "Sorry.";
// uncomment below and delete the above echo
//    header("HTTP/1.1 404 Not Found");
//    header("Location: 404.php");
//    exit;
}

Footnotes:

mysql_* functions deprecation notice:

http://www.php.net/manual/en/intro.mysql.php

This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

score 0 · Answer 2 · answered Apr 29 '14 at 04:10

Add a exit when you redirect the page

if($countKey != 1 ){
        header("Location: 404.php");
        exit;
}

and furthermore, its better to add a HTTP status to tell the browser the behavior your code to be, and do more friendly for SEO

if($countKey != 1 ){
        header("HTTP/1.1 404 Not Found");
        header("Location: 404.php");
        exit;
}

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

First, don't use the MySQL extension for reasons.

Second, you aren't doing any error checking or making sure that values have been set.

Here's a nice little PDO example to get you on the right path...

$pdo = new PDO('mysql:host=localhost;dbname=xxxxxx;charset=utf8', 'username', 'password', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false
]);

$ref = isset($_GET['ref']) ? $_GET['ref'] : null;
$stmt = $pdo->prepare(sprintf('SELECT 1 FROM `%s` WHERE confirmCode = ?', $tbl_name));
$stmt->execute([$ref]);
if (!$stmt->fetchColumn()) {
    http_response_code(404);
    header('Location: 404.php');
    exit;
}

edited May 23 '17 at 12:11

Community

1
1

answered Apr 29 '14 at 04:14

Phil

157,677
23
242
245

I'm looking at using PDO – user3143218 Apr 29 '14 at 04:37
@user3143218 You can also use *mysqli* but unless you need to use some very MySQL specific native functions, I find the PDO API easier to work with. Also, it throws exceptions which are easier to manage than checking all of your `mysqli_*` function return values. – Phil Apr 29 '14 at 04:45

PHP: Getting URL Referral Key

3 Answers3