Email is already in database, returns as if it isn't

Question

Here is some background information on what I'm trying to do here. I'm try to create a registration form for my website (successful so far until this point). Data can be entered into the DB. The only thing I'm trying to do now is prevent duplicate email/usernames from being entered into the db. Through much stackoverflow research, I have found and tested the following code:

$query = "SELECT COUNT(*) AS num_rows FROM users WHERE email = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $email);

if ($stmt->execute()) {
    return $stmt->num_rows;
}

What I then do is following:

if(user_exists($user_email) > 0) {
    echo "Email already exists!";
}

But is passes by this if statement as if the email does exist in the database! The email I'm trying to enter, for my tests is [email protected] which is already in the database! Would someone possibly point out where I have messed up in my code? Is there a silly mistake that I could have possibly done when trying to perform this?

Answer 1

The fix for your particular problem here is by not using COUNT(*) as mentioned by John and not depend on mysqli_stmt->num_rows by using a buffered result set:

$query = "SELECT * FROM users WHERE email = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $email);

return $stmt->execute() && $stmt->store_result() && $stmt->num_rows > 0;

Addendum

The only thing I'm trying to do now is prevent duplicate email/usernames from being entered into the db

You will want to use table constraints to prevent this (not just from the app, but anything else that can access the database). This has the added benefit of guarding against race conditions.

ALTER TABLE users ADD UNIQUE(email);

This will raise an error if you attempt to insert a row with an email value that already exists. You can check for this error on the application side and do whatever you want with it.

Answer 2

change:

if ($stmt->execute()) {
    return $stmt->num_rows;
}
What I then do is following:

if(user_exists($user_email) > 0) {
    echo "Email already exists!";
}

to

$j=0;
if ($stmt->execute()) {
    $j= $stmt->num_rows;
} else {
    echo "Email already exists!";
}

Answer 3

Your query will always return 1 row. COUNT(*) will return a result set even if only to report no rows match your query. As a result user_exists() always returns 1.

Change your query to:

$query = "SELECT * FROM users WHERE email = ?";

Now if no rows match your query $stmt->num_rows will be 0 so user_exists() will return 0.