php $stmt returns 0?

Question

Here is my code:

if (isset($_GET['email'], $_GET['code'])) {
    if ($stmt = $con->prepare('SELECT * FROM accounts WHERE email = ? AND activation_code = ?')) {
        $stmt->bind_param('ss', $_GET['email'], $_GET['code']);
        $stmt->execute();
        // Store the result so we can check if the account exists in the database.
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            // Account exists with the requested email and code.
            if ($stmt = $con->prepare('UPDATE accounts SET activation_code = ? WHERE email = ? AND activation_code = ?')) {
                // Set the new activation code to 'activated', this is how we can check if the user has activated their account.
                $newcode = 'activated';
                $stmt->bind_param('sss', $newcode, $_GET['email'], $_GET['code']);
                $stmt->execute();
                echo 'Your account is now activated! You can now <a href="index.html">login</a>!';
            }
        } else {
            echo 'The account is already activated or doesn\'t exist!';
        }
    }
}

I'm always getting this output:

echo 'The account is already activated or doesn\'t exist!';

What am I doing wrong here?

your logic is wrong, when the email with the code doesn't exist write the code, else it is already activated, also i would forst check if the email already exist prior to chekc if ot has an code — nbk, Sep 02 '23 at 00:44

score -1 · Answer 1 · answered Sep 02 '23 at 00:47

-1

Your query doesn't distinguish between the account not existing and existing with a different activation code. The query should just look for the email, and then check the activation code.

if (isset($_GET['email'], $_GET['code'])) {
    if ($stmt = $con->prepare('SELECT activation_code FROM accounts WHERE email = ?')) {
        $stmt->bind_param('s', $_GET['email']);
        $stmt->execute();
        // Store the result so we can check if the account exists in the database.
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            // Account exists with the requested email
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            if ($row['activation_code'] != $_GET['code']) {
                if ($stmt = $con->prepare('UPDATE accounts SET activation_code = ? WHERE email = ?')) {
                    // Set the new activation code to 'activated', this is how we can check if the user has activated their account.
                    $newcode = 'activated';
                    $stmt->bind_param('ss', $newcode, $_GET['email']);
                    $stmt->execute();
                    echo 'Your account is now activated! You can now <a href="index.html">login</a>!';
                }
            } else {
                echo 'The account is already activated.';
            }
        } else {
            echo 'The account doesn\'t exist!';
        }
    }
}

answered Sep 02 '23 at 00:47

Barmar

741,623
53
500
612

1

I'm getting this error: Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in /home/u590333097/domains/mysite.com/public_html/activate.php:21 Stack trace: #0 /home/u590333097/domains/mysite.com/public_html/activate.php(21): mysqli_stmt->get_result() #1 {main} thrown in /home/u590333097/domains/mysite.com/public_html/activate.php on line 21 – Ben5678 Sep 02 '23 at 00:53
Is it possible there are multiple rows with the same `email`? That should only happen if you haven't read all the rows from the first query. – Barmar Sep 02 '23 at 00:54
See https://stackoverflow.com/questions/3632075/why-is-mysqli-giving-a-commands-out-of-sync-error – Barmar Sep 02 '23 at 00:55
there is only 1 row with an email – Ben5678 Sep 02 '23 at 01:00
1

Then I'm not sure why it happens. `$stmt->store_result()` is also supposed to prevent this. Do you have any other queries in progress when you get to this code? – Barmar Sep 02 '23 at 01:03
no i don't..... – Ben5678 Sep 02 '23 at 01:31

php $stmt returns 0?

1 Answers1