php - 当我使用 (if,BEGIN,END) 时，sql 查询不起作用

Question

我有一个包含很多列的表。我试图构建一个将数据发送到该表的 php 文件。但在发送之前，它需要通过检查用户电子邮件来检查用户是否已经存在。我不知道如何修复该查询。

这是我的代码

<?php header('Content-Type: text/html; charset=utf-8');
$connection=mysqli_connect("localhost", "root", "", "users");

mysqli_query($connection,"SET character_set_client = utf8");
mysqli_query($connection,"SET character_set_connection = utf8");
mysqli_query($connection,"SET character_set_results = utf8");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$qry = "IF NOT EXISTS (SELECT * FROM `users` WHERE userEmail = $_post['userEmail'] ) BEGIN
INSERT INTO `users`( `userPassword`, `userFirstName`, `userLastName`, `userSex`, `userEmail`,
`userArea`, `userDate`, `userPicture`, `userPhoneNumber`, `userSkypeName`,`userDetails`, `userCategories`)
VALUES ( '" . $_POST["userPassword"] . "' , '". $_POST["userFirstName"] ."' , '". $_POST["userLastName"] ."',
" . $_POST["userSex"] . ", " . $_POST["userEmail"] . "  '" . $_POST["userArea"] . "' ,
 '". $_POST["userDate"] ."' , '". $_POST["userPicture"] ."',
 " . $_POST["userPhoneNumber"] . ", " . $_POST["userSkypeName"] . " , '" . $_POST["userDetails"] . "',
  '". $_POST["userCategories"] ."' )"
  END
  ;



mysqli_query($connection,$qry);

mysqli_close($connection);
echo $qry;



?>

这是我在 phpmyadmin 中尝试的查询

IF NOT EXISTS (SELECT * FROM `users` WHERE userEmail = "abc@gmail.com" ) BEGIN 
INSERT INTO `users`( `userPassword`, `userFirstName`, `userLastName`, `userSex`, `userEmail`,
`userArea`, `userDate`, `userPicture`, `userPhoneNumber`, `userSkypeName`,`userDetails`, `userCategories`)
VALUES ( "password" , "fName", "Lname",
"Male", "abc@gmail.com " ,"abcx" , "28-12-2016","pic address",
 "09368157474", "skypeEmail" , "some details","abdjdgjsbsjsbsksb" );
  END  ;

这是错误:

Answer 1

最简单的解决方案是创建一个 unique index在电子邮件栏上。如果用户使用现有的电子邮件地址进行注册，则唯一索引将阻止插入。通过查询返回的错误，很容易发现违反了唯一索引约束，因此您告诉用户该电子邮件地址之前已被使用过。