PHP how to check for email already in MySQL database?

Question

Hi I'm calling out for help from all the PHP Gods on Stackoverflow :)

I've created an email signup form (just 1 field for email), that is able to validate with Ajax and post a new email to the database from a basic PHP script I found.

However the next step I have to do is check if an email is already in the database before adding it. There are several questions exactly like this on Stack and I've tried all the answers however to no avail :( I'm not a PHP guy and haven't been able to hack it right yet.

Below is my current insert.php file which does work and does add a new email field into the database. However the code below that is the latest I've tried to use to check for an already existing email, but I get a send data error.

Working PHP file to add email

<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("mydatabase", $con);

$sql="INSERT INTO newsletter (email)
    VALUES
    ('$_POST[mail]')";

    if (!mysql_query($sql,$con)) {
        die('Error: ' . mysql_error());
    }

    echo "Thanks for subscribing!"; //Text on page
    //header("Location: /thankyoupage.php"); //Redirect page

mysql_close($con)
?>

UPDATED CODE using PDO Code below works to add emails, however still allows duplicates...

<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = 'root';
/*** email ***/
$email    = '$_POST[mail]';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mydatabase", $username, $password);

//$query = SELECT count(*) AS `total` FROM `data` WHERE `email` = '{$request}'

$query = SELECT COUNT(*) as 'count' FROM `data` WHERE email = '$_POST[mail]';
$row = mysql_fetch_assoc(mysql_query($query));

if($row['total']) {
    echo 'Sorry email already exists';
}
else {
    /*** echo a message saying we have connected & added email ***/
    echo 'Thanks for subscribing!';

    /*** INSERT data ***/
    $count = $dbh->exec("INSERT INTO newsletter(email) VALUES ('$_POST[mail]')");
}

/*** echo a message saying we have connected & added email ***/
//echo 'Thanks for subscribing!';

/*** INSERT data ***/
//$count = $dbh->exec("INSERT INTO newsletter(email) VALUES ('$_POST[mail]')");

/*** echo the number of affected rows ***/
/*echo $count;*/

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Thanks in advance for anyone with the time to take a look at this :)

Extra Notes: My database table is called newsletter and there are 2 fields (id - numbers only) & (email)

@Jim I've tried the answers here: http://stackoverflow.com/questions/4378814/how-to-check-for-duplicate-entry-in-db but I guess I'm just not verse enough in PHP — Leon Gaban, Mar 07 '12 at 17:35
Hey, you are **wide open** to SQL injection, and you **will** be hacked if you haven't been already. Learn to use prepared queries with PDO and avoid this problem entirely. — Brad, Mar 07 '12 at 17:36
+1 for PDO but looking at this pice of code, you may have to invest a piece of time to dig into PDO. Note: you can also do that straight setting up your mysql table, disabling "duplicates". Better, faster, cleaner. — hornetbzz, Mar 07 '12 at 17:37
@prodigitalson I'm an unsure about that, but I do have a column named ID and it only contains numbers. When I remove email entries from my tests, it does remove the ID. Example: I have ID's 1-5 then any new email I'm able to add will start at 45 or so... — Leon Gaban, Mar 07 '12 at 17:38
Trying the PDO Insert example here: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html#4.3 works so far to add the email in :) now just need to figure out the check — Leon Gaban, Mar 07 '12 at 18:05
I've just updated my question with PDO code that does work to add emails, however still adding duplicates — Leon Gaban, Mar 07 '12 at 18:31
Also, don't use the root account of mysql. set up a separate user for the DB. //off topic — Flukey, Mar 07 '12 at 18:34

score 1 · Accepted Answer · answered Mar 07 '12 at 17:41

1

if email is an unique key, that would be simple

<?php
mysql_connect("localhost","root","root");
mysql_select_db("howdini");
$email = mysql_real_escape_string($_POST['mail']);
$sql="INSERT IGNORE INTO newsletter (email) VALUES ('$email')";
mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
if (mysql_affected_rows()) {
  header("Location: /thankyoupage.php"); //Redirect page
} else {
  //already exists
}

answered Mar 07 '12 at 17:41

Your Common Sense

156,878
40
214
345

Just tried this exact code, I put echo 'thanks' in the IF and echo 'Sorry' in the else. Was able to add emails, but I did not get my error when I added d@d.com twice. – Leon Gaban Mar 07 '12 at 18:04
Just updated with working PDO code, but still able to add duplicates, I checked my table and I see that ID is my primary key, going to google how to create a unique key – Leon Gaban Mar 07 '12 at 18:32
+1 for PDO but looking at this piece of code, you may have to invest a piece of time to dig into PDO. Note: you can also do that straight setting up your mysql table, disabling "duplicates", using the ["UNIQUE " mysql statement](http://stackoverflow.com/a/5986161/461212). Better, faster, cleaner. – hornetbzz Mar 07 '12 at 18:54
Hey your code worked! After I finally figured out how to add a Unique Key :D thanks! Now I'm going to see if I can PDO your code. – Leon Gaban Mar 07 '12 at 20:00
What if email service allows dots in username, so that `name@server.com` equals `na.me@server.com` ?? – Davit Feb 25 '13 at 00:30

PHP how to check for email already in MySQL database?

1 Answers1

Linked