抛出SQLSTATE的唯一约束[23000]：完整性约束违规：1062由于唯一约束而重复[重复]

Question

This question already has an answer here:

• mysql insert error 1062 5 answers

In my mysql database I set the 'email' field as a unique constraint. I don't want two or more users to have the same email address. I created this function to check that. I only want the function to run if a different user tries to use the same address. This is the function:

    <?php

     function Email_gogo() {

    if(!empty($_POST['email']))
    {

     $mysql_hostname = '*****';
     $mysql_username = '*****';
     $mysql_password = '*****';
     $mysql_dbname = '*****';

     try {
   $db= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname",  `enter code here`$mysql_username, $mysql_password); 
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch (PDOException $e) {
     exit( $e->getMessage() );
      }
        $query_email = "
             SELECT
             email
             from users
             where
             email = :email
        ";

        $query_goes = array(

        ':email' => $_POST['email']

        );

        Try{
            $stmt = $db->prepare($query_email);
            $stmt ->execute($query_goes);
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)){

            }
        }
        catch(PDOException $ex){
            echo 'ERROR: '. $ex->getMessage();
        }
        if($stmt->rowCount() > 0){

            echo("That Email is already in use...");
        }


    }

    }

    ?>

This function is called up in a script that allows an admin to check users name, email, and last name. The username can't be changed. This is the script that I've updated. This is the script:

<?php


require("common.php");
require_once("gogo.php");

if(empty($_SESSION['user']))
{

    header("Location: ../hound/login.php");


    die("Redirecting to ../hound/login.php");
    }

if(!empty($_POST))
{

if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL))
{

    die("Please enter a valid email address...");
}

}
    Email_gogo();

    $array_value = array(
        ':email' => $_POST['email'],
        ':first_name' => $_POST['first_name'],
        ':last_name' => $_POST['last_name'],
        ':id' => $_POST['id']
     );



    $query = "UPDATE users 
        SET 
        email = :email,
        first_name = :first_name, 
        last_name = :last_name

        WHERE
          id = :id
    ";


   try
    {

        $stmt = $db->prepare($query);
        $result = $stmt->execute($array_value);
    }
    catch(PDOException $ex)
    {

        die("Ouch, failed to run query: " . $ex->getMessage());
    }



    header("Location: users.php");


    die("Redirecting to users.php");

    ?>

This is the error: That email is already in use (from the function). It does check if the email is in use, but it throws another error:

Ouch, failed to run query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicata du champ '[e-mail]' pour la clef 'email'

(this error is from the script).

I called up the function in the middle of the script. My issue is were should I use the function, and have it only run when a user tries to use the same address. Thank you in advance.

</div>

Answer 1

The problem is that your function does not actually return anything, it just displays an error message after which PHP will just continue its normal execution. So your "update" query will be executed regardless of whether the email is in use or not. This is what you should do in Email_gogo

function Email_gogo() 
{
    if(!empty($_POST['email']))
    {
        $mysql_hostname = '*****';
        $mysql_username = '*****';
        $mysql_password = '*****';
        $mysql_dbname = '*****';

        try
        {
            $db= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname",  `enter code here`$mysql_username, $mysql_password); 
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } 
        catch (PDOException $e) 
        {
            exit( $e->getMessage() );
        }

        $query_email = "
        SELECT
        email
        from users
        where
        email = :email
        ";

        $query_goes = array(

        ':email' => $_POST['email']

        );

        try
        {
            $stmt = $db->prepare($query_email);
            $stmt ->execute($query_goes);
        }
        catch(PDOException $ex)
        {
            echo 'ERROR: '. $ex->getMessage();
        }

        if($stmt->rowCount() > 0)
            return false;
        else
            return true;
    }
}

Then when you call it later:

if(Email_gogo())
{
    $array_value = array(
        ':email' => $_POST['email'],
        ':first_name' => $_POST['first_name'],
        ':last_name' => $_POST['last_name'],
        ':id' => $_POST['id']
     );



    $query = "UPDATE users 
        SET 
        email = :email,
        first_name = :first_name, 
        last_name = :last_name

        WHERE
          id = :id
    ";


   try
    {

        $stmt = $db->prepare($query);
        $result = $stmt->execute($array_value);
    }
    catch(PDOException $ex)
    {

        die("Ouch, failed to run query: " . $ex->getMessage());
    }



    header("Location: users.php");


    die("Redirecting to users.php");
}
else
    die("Email address already in use");

Also, the "while" loop in your function is completely unnecessary. You can just remove it. The rowCount() method does not need to loop through every record to know how many there are.

EDIT: Added the full code for better clarity.