Check for duplicate emails before querying

Question

I've searched and look at other examples but as i'm new i'm having trouble translating over to my code. What I want to do is have it check the database to see if that email has already been entered. If it has I want it to tell them it has and that there is only one entry per person. Any help would be greatly appreciated. Keep in mind this is the first thing I've ever coded that connects to a database.

<?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);

}
?>

First comment: If this is the first thjing you've ever coded that connects to a database, do it properly. Don't use `mysql` - it's deprecated. Rework your code to use `mysqli` (straightforward) or `PDO`, (but that's more involved) — , Sep 02 '13 at 22:58
2 options, do a select first or make email a unique index, always try the insert, and check if it failed due to being a duplicate — , Sep 02 '13 at 22:59

Ben · Accepted Answer · 2013-09-03T13:16:29.977

First things first: Switch over to Prepared Statements.

They are much safer and a more advanced way to access your database.

<?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;

?>

What I did is execute a separate query first searching for any entries already existing in the table with the user's email address. So long as nothing's found, the script continues.

Hopefully this has also given you an insight into how to execute prepared statements. These make sure that your database can't be tampered with using injections, which is a lot off your back and ensures you can focus on coding efficient scripts rather than sanitizing user inputs.

My first post on stackoverflow and I'm amazed. Thank you for your extensive comments. Where can I find more info on the use of PDO, any good online links? — user2733862, Sep 03 '13 at 01:08
when using your sample I am receiving the error Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/content/88/10316188/html/contest/data.php on line 43 — user2733862, Sep 03 '13 at 01:22
nevermind i solved it by adding the line $stmt->bindParam(':email',$email); — user2733862, Sep 03 '13 at 01:44
You can find more about Prepared Statements and PDO [here](http://en.wikipedia.org/wiki/Prepared_statement) and [here](http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks), as well as the link at the top of my answer. If it helped you don't forget to select it as your accepted answer by clicking the tick :) — Ben, Sep 03 '13 at 11:25

Check for duplicate emails before querying

1 Answers1