i have a table with many columns. i trying to build a php file that will send data to that table. but before it sends it needs to check if the user already exist by checking user email. i don't know how to fix the query.
this is my code
<?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;
?>
this is the query i tried in 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 ;
this are the errors:
The simplest solution would be to create a unique index on the email column. If a user were to register with an existing email address, then the unique index would prevent the insertion. By querying the returned error it is easy to see that a unique index constraint was violated, therefore you tell the user that the email address had been used before.