php - 我一直收到这个 : Integrity constraint violation while trying to verify if email exists in database

Question

我正在创建一个用户管理系统。我可以编辑用户。我可以创建用户。我可以验证电子邮件的格式是否正确。但是，我的问题是验证数据库中是否存在相同的电子邮件。我不断收到此错误:哎哟，无法运行查询:SQLSTATE[23000]:违反完整性约束:1062 Duplicata du champ 'Markr@fun.com' pour la clef 'email'。此代码如下。第一个是用于将信息存储到数据库的表单。第二个是按下提交按钮后运行的脚本。

<?php

require("../scripts/connect.php");


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

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


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

$query_parm = array(

':id' => $_GET['id']

 );


 $query = "

SELECT
*
FROM users 
WHERE 
id = :id
";


try
{
 $stmt = $db->prepare($query);
 $stmt->execute($query_parm);

}
catch (PDOException $ex)
{

die("Failed to run query: " . $ex->getMessage());

}

  $rows = $stmt->fetchAll();


  ?>


  <form action="../scripts/edit_users.php" method="post">

<?php foreach($rows as $row): ?>

Username:<br />
<b><?php echo htmlentities($row['username'], ENT_QUOTES, 'UTF-8'); ?></b>
<br /><br />
<input type="hidden" name="id" value="<?php htmlentities($row['id'],  ENT_QUOTES, 'UTF-8'); ?>">
First Name:<br />
<input type="text" name="first_name" value="<?php echo `enter code he  htmlentities($row['first_name'], ENT_QUOTES, 'UTF-8'); ?>" />
<br /><br />
Last Name:<br />
<input type="text" name="last_name" value="<?php echo htmlentities ($row['last_name'], ENT_QUOTES, 'UTF-8'); ?>" /> 
<br /><br />
E-Mail Address:<br />
<input type="text" name="email" value="<?php echo htmlentities($row     ['email'],ENT_QUOTES,'UTF-8'); ?>" /> 
<br /><br />
Password:<br />
<input type="password" name="password" value="" /><br />
<br /><br />
<input type="submit" value="Update User" />
<a href="../scripts/users.php">Back</a><br />
 <?php endforeach; ?>
 </form>

这是在按下提交时运行的脚本。

<?php


require("common.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...");
}

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

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

    $query_goes = array(

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

    );
    try
    {

        $stmt = $db->prepare($query_email);
        $result = $stmt->execute($query_goes);
    }
    catch (PDOException $ex)
    {
        die("Failed to run query: " . $ex->getMessage());
    }
    $row = $stmt->fetch();
    if($row) 
    {
        die("That email is already in use...");
    }

}
}


  $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");

   ?>

Answer 1

你究竟想在这里做什么？

if($_POST['email'] !=$_POST['email'])

这是一个不可能的条件。 “如果这个东西不是它自己”。

所以你检查一个电子邮件地址是否存在永远不会被执行，然后你盲目地尝试插入它。

此外，这不是您进行此类检查的方式。即使代码结构正确，也NO保证某些并行脚本无法在此脚本执行其select< 的(短)间隔内插入完全相同的电子邮件地址 然后是 insert。

你应该做一个无条件插入，并检查它是否成功，例如

if ($_POST) {
    $sql = "INSERT ..."
    try {
       ...execute...
    catch (PDOException $e) {
       if ($e->getCode() == 1062) // duplicate key violation
          ... email is a dupe
       }
    }
}