I want to make an simple register form where I want to check if the email is already used and if not insert the data in my db. if I submit the form I get this fatal error "Integrity constraint violation: 1062 Duplicate entry" but the data is submitted anyway, and I can see it in my db. If i only insert the data or check if the email is already used i don't get this error.maybe someone can help me with this. this is the code:

html

<form action="test.php" method="POST">
    <input type="text" name="name" placeholder="name" autofocus="on">
    <input type="text" name="email" placeholder="email">
    <input type="submit">
</form>

php

try
{
    $pdo = new PDO($attr, $user, $pass, $opts);
}
catch (PDOException $e)
{
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}

if($_POST)
{
    $name = $_POST['name'];
    $email = $_POST['email'];

    $emailAbfrage = "SELECT email FROM accounts";
    $result = $pdo->query($emailAbfrage);
    while($row = $result->fetch())
    {
        if($email == $row['email'])
        {
            echo "email ist schon vergeben";
        }
        else 
        {
            $insert = $pdo->prepare("INSERT INTO `accounts`(`kundenname`, `email`) VALUES (?,?)");
            $insert->execute(array($name, $email));
        }
    }
}

CodePudding user response：

There are a few things wrong with the code snippet:

1. You loop over all the accounts. If you get a match then you print email is schon vergeben but you don't break so it will still loop over all the other accounts, where you will get to the else, try to insert the account again, and get the Duplicate Entry exception.
2. Your query literally returns all the accounts in the database. A better one would be SELECT email FROM accounts WHERE email=? which would just return the one account you care about.
3. Even better than doing a SELECT, you can do INSERT INTO accounts(kundenname, email) VALUES (?,?) ON DUPLICATE KEY UPDATE email=VALUES(email). This does an insert only if the account doesn't exist. You can check the returned row count to see if it added a new record or not. Change the part after ON DUPLICATE KEY UPDATE... to suite your needs in the case that the account is already there.