PDO with MySQL not working in email activation

Question

Okay, so I'm setting up the activation page using $_GET[] from the link the server emails the user.

Here's my activation page.

if (isset($_GET['success']) && $_GET['success'] == false) {
        echo 'Your account has been activated, please login to continue.';
    } else if (isset($_GET['email'], $_GET['email_code']) === true) {
        $email          = trim($_GET['email']);
        $email_code     = trim($_GET['email_code']);

        if (email_exists($db, $_GET['email']) == false) {
            $errors[] = 'This email address hasn\'t been registered with us.';
        } else if (activate($db, $email, $email_code) === false) {
            $errors[] = 'We had problems activating your account, please contact an Administrator.';
        }

        if (empty($errors) === false) {
            echo output_errors($errors);
        } else {
            header('Location: activate.php?success');
            exit();
        }
    } else {
        header('Location: index.php');
    }

I believe that to be fine, the problem lies within my function activate()

    function activate(PDO $db, $email, $email_code) {
$stmt = $db->prepare("SELECT COUNT (`id`) FROM `users` WHERE `email` = :email AND `email_code` = :email_code AND `active` = 0");
$stmt->bindValue(':email', $email);
$stmt->bindValue(':email_code', $email_code);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_OBJ);

return $row ? $row->type : 0;
}

At this moment, I'm just trying to get it to return something, yet it doesn't.

What I really need, is for it to do this.

function activate($email, $email_code) {
$email          = mysql_real_escape_string($email);
$email_code     = mysql_real_escape_string($email_code);

if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) ==1) {
    mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
    return true;
} else {
    return false;
}
}

But I cannot quite translate it.

Any help would be appreciated, thanks.

I thought I'd add this doesn't return any errors, mainly because I haven't put anything in correctly yet for it to return one.

EDIT:

else if (activate($db, $email, $email_code) === 0) {
            $errors[] = 'We had problems activating your account, please contact an Administrator.';
        }

Then the function

function activate(PDO $db, $email, $email_code) {
$sql  = "SELECT `active`, `email_code` FROM `users` WHERE `email` = '?'";
$stmt = $db->prepare($sql);
$stmt->execute(array($email));
$row  = $stmt->fetch();
if ($row && $row['active'] == $email_code && !$row['active'] ) {
    $sql  = "UPDATE `users` SET `active` = 1 WHERE `email` = '?'";
    $stmt = $db->prepare($sql);
    $stmt->execute(array($email));
    return $stmt->rowCount();
} else {
    return 0;
}
}

why don't you run an update query in PDO version of activate()? — Your Common Sense, May 09 '13 at 07:39
My aim is to, but it also needs to return a boolean true/false for it to pass the error checking. — user2305310, May 09 '13 at 14:40
though you don't have to use strict comparison operator === in this case. Better use regular `==` — Your Common Sense, May 09 '13 at 15:37

score 2 · Accepted Answer · answered May 09 '13 at 14:54

2

function activate(PDO $db, $email, $email_code) {
    $sql  = "SELECT active, email_code FROM users WHERE email = ?";
    $stmt = $db->prepare($sql);
    $stmt->execute(array($email));
    $row  = $stmt->fetch();
    $if ($row && $row['active'] == $email_code && !$row['active'] )
        $sql  = "UPDATE users SET active = 1 WHERE email = ?");
        $stmt = $db->prepare($sql);
        $stmt->execute(array($email));
        return $stmt->rowCount();
    }
}

answered May 09 '13 at 14:54

Your Common Sense

156,878
40
214
345

Thanks very much for your answer, and this works perfectly. Could you give me an insight as to how you got there? If you've got time. Just to educate me for future problems like this? – user2305310 May 09 '13 at 15:02
In fact, this doesn't work at all for me. I'll update my question. – user2305310 May 09 '13 at 15:23
there 2 comments are quite inconsistent. Anyway: http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858 – Your Common Sense May 09 '13 at 15:31

PDO with MySQL not working in email activation

1 Answers1

Linked