Prevent duplicate data being entered into mysql database

Question

I'm trying to make my email subscription service reject emails that already exist within my database so users don't subscribe the same email twice. this is what I have but its not working, any ideas?

<?php 
    if(!isset($_POST['submit'])) 
        exit();

    $vars = array('email');
    $verified = TRUE;
    foreach($vars as $v) {
        if(!isset($_POST[$v]) || empty($_POST[$v])) {
            $verified = FALSE;
        }
    }   
    if(!$verified) {
        echo "<p style='color:white; margin-top:25px;'>*Email required*</p>";
        exit();
    }
    $email = $_POST['email'];
    if($_POST['submit']) echo "<p style='color:white; margin-top:25px;'>*Check your inbox*        </p>";

    // Create connection
    $con=mysqli_connect("mysql.host","user","password","dbname");

    // Check connection
    if (mysqli_connect_errno($con))
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $sql="INSERT INTO emails (email)    VALUES  ('$_POST[email]')";
    if (!mysqli_query($con,$sql))
    {
        die('Error: ' . mysqli_error($con));
    }

    $query = mysql_query("SELECT * FROM emails WHERE email='$email'",($con));
    if(mysql_num_rows($query) != 1)
    {
        echo "email already exists";
        // redirect back to form and populate with 
        // data that has already been entered by the user
    }


    mysqli_close($con);
?>

What does 'not working' mean? For one thing - your check to see if the email exists is after you try to insert it. For another, you seem to be mixing mysql_ and mysqli_ functions. — andrewsi
– andrewsi, Commented Nov 19, 2013 at 20:33
@Fred-ii- - I hope you didn't take my comment on your answer seriously! — andrewsi
– andrewsi, Commented Nov 19, 2013 at 20:44

Chris Laarman · Accepted Answer · 2013-11-19 20:34:16Z

3

The easiest way to let MySQL reject the duplicate e-mail address is to make the field unique (http://www.w3schools.com/sql/sql_unique.asp)

ALTER TABLE emails ADD UNIQUE (email)

However, MySQL will not return a warning

answered Nov 19, 2013 at 20:34

Chris Laarman

1,62713 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noam Rathaus Over a year ago

I think this approach is best, having 'email' a unique field will prevent redundant checking code, just try to insert, if you get an error due to DUPLICATE KEY you know you already have that record. Also since 'email' is a UNIQUE KEY now, it is a very good candidate for searching.

Chris Laarman Over a year ago

Because?..:) I know it's not the best site overall, but in this particular case their explaination was pretty clear

Krish R · Accepted Answer · 2013-11-19 20:45:09Z

1

Use mysqli_num_rows($query) instead of mysql_num_rows($query)

       $query = mysqli_query($con, "SELECT * FROM emails WHERE email='".$email."'");

        if(mysqli_num_rows($query) > 0){

            echo "email already exists";
        }else{
            $sql="INSERT INTO emails (email)    VALUES  ('".$_POST[email]."')";
            if (!mysqli_query($con,$sql))
            {
                die('Error: ' . mysqli_error($con));
            }
        }

edited Nov 19, 2013 at 20:45

answered Nov 19, 2013 at 20:36

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

Comments

Funk Forty Niner · Accepted Answer · 2013-11-19 21:30:04Z

1

Firstly, you're mixing MySQLi_ with MySQL_ so stick with MySQLi_ and modify the rest of your code accordingly.

This is the logic I use in my scripts, using ? instead of '$email'

$query = $con->query("SELECT * FROM emails WHERE email=?");
// $query = $con->query("SELECT email FROM emails WHERE email=?");
// you may need to use that one --^ if checking a particular column

$numrows=mysqli_num_rows($query);

if($numrows > 0){
die("Email already exists in the database, please try again.");
}

You can use this method, binding parameters. Assuming your column is named email

$query = "SELECT email FROM emails WHERE email=?";

if ($stmt = $con->prepare($query)){

        $stmt->bind_param("s", $email);

        if($stmt->execute()){
            $stmt->store_result();

            $email_check= "";         
            $stmt->bind_result($email_check);
            $stmt->fetch();

            if ($stmt->num_rows == 1){

            echo "That Email already exists.";
            exit;

            }
        }
    }

edited Nov 19, 2013 at 21:30

answered Nov 19, 2013 at 20:40

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

3 Comments

andrewsi Over a year ago

No parameter binding? Shocking! :D

Funk Forty Niner Over a year ago

Undeleted. @andrewsi better yes?

Funk Forty Niner Over a year ago

added a bind_param feature ;-) @andrewsi

Community · Accepted Answer · 2017-05-23 12:19:12Z

0

Beside mixing mysql and mysli

Use > not !=

if(mysqli_num_rows($query) > 1)

But this approach means you already have duplicates.

Maybe this will help after you put an unique index on the email column.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Nov 19, 2013 at 20:34

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Comments

Dave · Accepted Answer · 2013-11-19 20:38:54Z

0

As noted in the other answers, you mixed mysqli and mysql functions.

for exemple in both these lines you use mysql instead of mysqli functions.

$query = mysql_query("SELECT * FROM emails WHERE email='$email'",($con));
if(mysql_num_rows($query) != 1)

I also think your code is easily SQL Injectable.

You are using $_POST["email"] in your insert query, without sanitizing it.

Have a look to at least the sql injection wikipedia page

answered Nov 19, 2013 at 20:38

Dave

3,0144 gold badges39 silver badges55 bronze badges

3 Comments

Noam Rathaus Over a year ago

Actually his fault is not, not filtering it, rather using it inside his SQL instead of passing it as arguments. Filtering is not the right way to protect from SQLi, using argument based SQL (i.e. "email = ?") and then to the SQL function the email as an argument is the right approach

Dave Over a year ago

Interesting, can you provide some details or a source

Noam Rathaus Over a year ago

You are asking me? :) if so I am referring to this: php.net/manual/en/pdo.prepare.php

Rogue · Accepted Answer · 2013-11-19 20:50:36Z

0

My answer would be as follows,

First, create a UNIQUE KEY of the email column, and then:

INSERT INTO `table` VALUES (/*etc*/) ON DUPLICATE KEY UPDATE /*set a column equal to itself*/

This allows you to attempt inserting into the database, and you can choose whether or not the query throws an error. If you want it to throw an error, then simply do not use ON DUPLICATE KEY, and then catch the SQLException that is thrown from the query and tell the user that the email already exists.

answered Nov 19, 2013 at 20:50

Rogue

11.5k6 gold badges51 silver badges74 bronze badges

Comments

gravidThoughts · Accepted Answer · 2013-11-19 21:04:16Z

0

Add a unique constraint to the email column.

Test for error returned on insert or update. I believe the code may be influenced if it is a primary key, foreign key, unique constraint on an index.

With PHP you can use

if( mysql_errno() == X) {
    // Duplicate VALUE
} else {
    // fail
}

You can test it yourself with a duplicate email or here are the mysql_errNo return values

For non PHP, to determine correct error code test it yourself with a duplicate email or look at the following.

MySQL Errors

edited Nov 19, 2013 at 21:04

answered Nov 19, 2013 at 20:52

gravidThoughts

6335 silver badges19 bronze badges

Collectives™ on Stack Overflow

Prevent duplicate data being entered into mysql database

7 Answers 7

2 Comments

Comments

3 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

3 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related