php - 在查询之前检查是否有重复的电子邮件

Question

我已经搜索并查看了其他示例，但由于我是新手，所以在转换为我的代码时遇到了问题。我想要做的是让它检查数据库以查看是否已经输入了该电子邮件。如果有，我希望它告诉他们它有，并且每人只有一个条目。任何帮助将不胜感激。请记住，这是我编写的第一个连接到数据库的代码。

<?php //data.php


// Get values from form
$name        = $_POST['name'];
$email        = $_POST['email'];

$user_name = "user";
$password = "password";
$database = "dbname";
$server = "ahostsomewhere";

$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
$SQL = "INSERT INTO contestant_drawing (name, email) VALUES ('" . $name . "', '" .    $email . "')";
$result = mysql_query($SQL);
echo "To finalize your entry like our FaceBook Page, Good Luck!";

mysql_close($db_handle);


}else {
print "Database NOT Found ";
mysql_close($db_handle);

}
?>

Answer 1

首先要做的事:切换到 Prepared Statements .

它们是更安全、更高级的访问数据库的方式。

<?php

// Get values from form
$name        = $_POST['name'];
$email       = $_POST['email'];

$user_name    = "user";
$password     = "password";
$database     = "dbname";
$server       = "ahostsomewhere";

//Connect to your database using PDO (this only needs to be done once). $dbh is our connection
try {
    $dbh = new PDO("mysql:host=$server;dbname=$database", $user_name, $password);
}

//Make sure there are no errors
catch(PDOException $e){
    echo($e->getMessage());
}

//Query to check if the email already exists    
//This prepares the statements and uses placeholders (designated with a ':' colon)
$stmt = $dbh->prepare("SELECT * FROM `contestant_drawing` WHERE `email`=:email")
//This then binds a string to the placeholder (note the string '$stmt' is constant here)
$stmt->bindParam(':email',$email);
//Finally we execute the query
$stmt->execute();

//Count the rows in the returned array to see if there are already matching values in the database
if($stmt->rowCount()!=0){
    //Email already registered. Exit with a message
    exit('Email already exists');
}

//Email OK, continue with your queries
//You can use the same string '$stmt' because we don't need the query from before anymore. If you had multiple queries running alongside one another then you could use different strings for $stmt ($stmt1, $stmt2, $foo, $bar etc) but we can keep it the same to keep things simple
$stmt = $dbh->prepare("INSERT INTO `contestant_drawing`
                        (`name`, `email`)
                        VALUES (:name, :email)");
$stmt->bindParam(':name',$name);
$stmt->bindParam(':email',$email);
$stmt->execute();

echo "To finalize your entry like our FaceBook Page, Good Luck!";

//Disconnect from the database ($dbh)
$dbh = NULL;

?>

我所做的是执行一个单独的查询，首先使用用户的电子邮件地址搜索表中已经存在的任何条目。只要未找到任何内容，脚本就会继续。

希望这也能让您深入了解如何执行准备好的语句。这些可确保您的数据库不会因使用注入(inject)而被篡改，这会大大减轻您的负担，并确保您可以专注于编写高效的脚本，而不是清理用户输入。