我使用下面的命令将新行插入MySQL数据库。对此的查询存储在一个PHP文件（ajax.php）中，输入值通过jQuery中的Ajax调用传递给它。
一切都按预期进行，但如果数据库中已经有电子邮件，我想给用户返回一条消息。我知道如何使用ON DUPLICATE KEY更新数据库行，但有人能告诉我如何检查它，并在已经存在的情况下回显它吗（即不更新它）
（电子邮件是一个单一的主键，所以我只需要检查一下这个栏。）
我的PHP：
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$dob = $_POST["dob"];
$sql = "INSERT INTO Users (email, dob) VALUES ('" . $email . "', '" . $dob . "')";
if ($conn->query($sql)){
echo 'DB Update successful';
}else{
echo 'DB Update failed';
}
$conn->close();
插入之前只需要一个简单的SELECT调用。
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$dob = $_POST["dob"];
$sql = "SELECT email FROM Users WHERE email = " .$email;
$query = $conn->query($sql);
if (mysqli_num_rows($query) > 0){
echo "There exists an user with this email";
}
else {
$sql = "INSERT INTO Users (email, dob) VALUES ('" . $email . "', '" . $dob . "')";
if ($conn->query($sql)) {
echo 'DB Update successful';
}
else {
echo 'DB Update failed';
};
}
$conn->close();
简单的方法就是计算表中与您试图存储的电子邮件相同的条目的出现次数。计数而不是选择意味着它更快，并且只返回一个计数，而不是一个完整的行，然后您必须查看或计数结果中的行。
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
die("Connection failed: " . $conn->connect_error);
}
$stmt = $Conn->prepare("Select Count(email) as cnt FROM Users WHERE email = ?");
$stmt->bind_param('s', $_POST["email"]);
$stmt->execute();
$stmt->bind_result($cnt)
$stmt->close();
if ( $Cnt > 0 ) {
// its already there so do whatever you want in this case
} else {
$sql = "INSERT INTO Users (email, dob) VALUES (?,?)";
$stmt = $Conn->prepare($sql);
$stmt->bind_param('ss', $_POST["email"],$_POST["dob"]);
if ($stmt->execute(){
echo 'DB Update successful';
}else{
echo 'DB Update failed';
}
}
$conn->close();