当尝试更新时,PHP的重复输入错误


Duplicate Entry Error with PHP When Trying UPDATE

我正在检查记录是否存在,如果不存在则创建新记录,否则更新。SQL在PHPMyAdmin中工作得很好,但不能通过PHP页面。

$check_for_id = mysql_query("SELECT id FROM Members WHERE ID = '$id'");
if(mysql_num_rows($check_for_id) == 0) {
     // Not found, add a new record
     $sql="INSERT INTO Members (ID, Title, FirstName, LastName) VALUES ('$id', '$title', '$firstname', '$lastname')";
     $response=" Record Added";
} else {
    // Member is already in the database, so let's just update the info
    $sql="UPDATE Members 
            SET ID='$id', Title='$title', FirstName='$firstname', LastName='$lastname' 
            WHERE ID='$id'";
    $response=" Record Updated";
}

if (!mysqli_query($dbconnect,$sql)) {
  die('Error: ' . mysqli_error($dbconnect));
}
echo mysqli_affected_rows($dbconnect) . $response;

MySQL有一个语法叫做INSERT…ON DUPLICATE KEY UPDATE完全符合您的要求,只需1个查询而不是3个。还有REPLACE,虽然它略有不同(REPLACEINSERTDELETE + INSERT, INSERT ODKUINSERTUPDATE,如果您有一个auto_increment,结果可能会有所不同)。它将以事务方式完成,不像在您的代码上。它还假设id是主键或唯一键。