Duplicate Email check in MySQL

OK I figured out how to check for duplicates using an unique index in MySQL.

    try {
        $this->query = 'INSERT INTO users (username, status, password, security, email, date_added) VALUES (:username, :status, :password, :security, :email, Now())';
        $this->stmt = $pdo->prepare($this->query);
        $this->result = $this->stmt->execute([':username' => $data['username'], ':status' => $status, ':password' => $this->pwd, ':security' => 'newuser', ':email' => $data['email']]);
    } catch (Exception $e) {

        die('Invalid Text');
        //echo 'Caught exception: ', $e->getMessage(), "\n";
    }
    
    return true;

However, it works fine if there is no duplicate email address, but if there is a duplicate email it flags an exception error which is a good thing. Though obviously I don’t want to display an error message on a production server and I also don’t want a blank screen leaving the user confused. How, can get back to the registration screen if there is a duplicate? I hope I am making myself clear? I thought the whole purpose of a try-catch block was to do this sort of thing? Though no matter what I tried I just can’t figure it out and that is even after doing many internet searches. Thanks for the help in advance.

OK, I got it to work, BUT I have been reading that they say it is unwise to use PDOException on the internet. The php.net website says " You should not throw a PDOException from your own code."

Well, I’m not throwing the error, so would the following be OK?

try {
    $this->query = 'INSERT INTO users (username, status, password, security, email, date_added) VALUES (:username, :status, :password, :security, :email, Now())';
    $this->stmt = $pdo->prepare($this->query);
    $this->result = $this->stmt->execute([':username' => $data['username'], ':status' => $status, ':password' => $this->pwd, ':security' => 'newuser', ':email' => $data['email']]);
} catch (\PDOException $e) {
    //echo 'Caught exception: ', $e->getMessage(), " code " , $e->getCode(),  "<br>\n";
    if ($e->getCode() !== 2300) {
        return false; // 2300 Duplicate Entry:
    }
}

Re-throwing the one your code caught is not creating and throwing one from your own code.

The pseudo logic would be -

catch (\PDOException $e)
{
    // an error occurred
    if the error number is for something that this code is designed to handle, i.e. a duplicate index, handle it by telling the user what was wrong with the data they submitted
        // failure due to a specific error number that can be recovered from by the visitor submitting a different value
        return false;
    else
        // the error is for something else, either due to a programming mistake or not validating input data properly, that the visitor cannot do anything about or needs to know about
        throw $e; // re-throw the exception and let the next higher exception handler, php in this case, catch and handle it
}

// success
return true;

The sqlstate value for an integrity violation is 23000 (not 2300), you would test for an equal value (not a !==), and there are at least 17 current error numbers that fall under that value. The error number, which is what you should be testing for, is 1062 for inserting a duplicate value for a unique index. The error number can be accessed in the $e->errorInfo[1] property.

1 Like

Thanks, That cleared it up and I have to say just getting a handle on exceptions is/was confusing to me.

Sponsor our Newsletter | Privacy Policy | Terms of Service