PHP/MySQL - Prepared Statements - Is the usage in this example correct?

Question

I am creating an part of a website that deals with confirmation of a user subscribing to a newsletter.

I am having trouble with the usage on prepared statements when selecting data.

This is basically a check against information that was sent to the user in an email and retrieved by getting the info from the entered url.

So there is a string or 'key' in the database that is sent to the user in an email as a link to a page on my site with the users details appended to the url. The script checks to see if these keys match

The problem is that when I run the script it will trigger an error. This says "wrong key".

The key in the database ($dbkey) is the same as the key provided in the email link. which is the same key that is made into $key. The problem though, is that in the while loop an error is being triggered and $dbkeyis not being passed the data from the database :

Notice: Trying to get property of non-object in C:\wamp\www\site\script.php on line 35

The sql statement when run in phpmyadmin does return the correct result set.

Here is the code:

$confirm= sanitize($_GET['confirm']);
$stmt = $link->prepare("SELECT id, dbkey FROM specials WHERE id = ?");
if (!$stmt)
{
    $error = "{$link->errno}  :  {$link->error}";
    include "$docRoot/html/main/error.html.php";
    exit();
}
if (!$stmt->bind_param("i", $confirm))
{
    $error = "{$stmt->errno}  :  {$stmt->error}";
    include "$docRoot/html/main/error.html.php";
    exit();
}
if (!$stmt->execute())
{
    $error = "{$stmt->errno}  :  {$stmt->error}";
    include "$docRoot/html/main/error.html.php";
    exit();
}

$stmt->store_result();

if ($stmt->num_rows)
{
    while ($row = $stmt->fetch())
    {
    $dbKey = $row->dbkey;
    }
   $key= sanitize($_GET['key']);

    if ($dbKey !== $key)
    {
        echo 'wrong key';
    }
}
else
{
    echo 'not in database';
}

I would like to say that all other scripts connecting to the database in this manner do work, but this was the first time I have used prepared statements to select data. I wonder if this problem is caused by an error in my coding, hence the reason why I have posted this question.

If anyone could spot where I have gone wrong here, or possibly possibly provide some advice on how I would debug the code to see what exactly the error is that would be greatly appreciated!

Thanks!!

EDIT: The problem simply is the $key returns a string but $dbkey returns empty

EDIT2:

if ($stmt = $link->prepare("SELECT id, verified, dbkey FROM specials WHERE id=?")) {

    $stmt->bind_param("i", $confirm);
    $stmt->execute();
    $stmt->bind_result($dbId, $dbVerified, $dbKey);
    $stmt->fetch();
    $stmt->close(); 

    if ($dbKey !== $key)
    {
        echo 'wrong key';
    }
    else if ($dbVerified == 1)
    {
        echo 'already activated';
    }
    else if ($dbKey == $key && dbVerified == 0)
    {
        echo 'success';
    }
}
else
}
    echo 'user not in db';
}

Answer 1

$stmt->fetch() just returns a boolean indicating whether it was successful, not an object whose properties are the current row's fields. You need to call $stmt->bind_result() to specify into which variables you want the fields to be placed.

The approach taken in your second edit looks good, except that the test for whether the user is in the database should be onfetch(), not prepare() (or else use num_rows as you had previously). Thus:

if ($stmt = $link->prepare("SELECT id, verified, dbkey FROM specials WHERE id=?"))
{
    $stmt->bind_param("i", $confirm);
    $stmt->execute();
    $stmt->bind_result($dbId, $dbVerified, $dbKey);

    if ($stmt->fetch())
    {
        if ($dbVerified == 1)
        {
            echo 'already activated';
        }
        else if ($dbKey !== $key)
        {
            echo 'wrong key';
        }
        else if ($dbKey == $key && dbVerified == 0)
        {
            echo 'success';
        }
    }
    else
    }
        echo 'user not in db';
    }

    $stmt->close();
}