Test for duplicates in a Mysql database?

Question

so I have a simple newsletter signup form. I can add users into the database, but I want to account for duplicate emails. This is what I have right now.

  $result = mysql_query("SELECT * FROM Users WHERE Email='$email'");


  $num_rows = mysql_num_rows($result);


  if($num_rows){
    echo "You've already registered! Thanks anyway";
  }

  else{

  mysqli_query($db_connection, "INSERT INTO Users (Name, Email) VALUES('$name' , '$email')");
  echo "Thanks for signing up, $name.";
  }

When I click submit, it will add it to the database, regardless if the email is the same.

The mysql_query that gets $result is fine. However, for some reason the if statement doesn't get called. Any idea why?

That's because you're mixing MySQL APIs so your second query which will always be executed, plus this `if($num_rows)` should be `if($num_rows >0)` — Funk Forty Niner, Sep 30 '14 at 22:17
Might be better to enforce this on the DB side. Make email unique in the table. — Andreas, Sep 30 '14 at 22:20

score 3 · Answer 1 · edited May 23 '17 at 11:59

"The mysql_query that gets $result is fine"

Your DB connection sounds to be mysql_ - and you're using mysqli_ functions below that.

Use mysqli_ exclusively and do not mix them, they don't.

Sidenote: Your question is confusing.

You state: When I click submit, it will add it to the database, regardless if the email is the same. - then you say The mysql_query that gets $result is fine.

This: if($num_rows) should be if($num_rows >0)

Use this for your DB connection:

$db_connection = mysqli_connect("your_host","user","password","your_db");

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

then do:

$result = mysqli_query($db_connection, "SELECT * FROM Users WHERE Email='$email'");

$num_rows = mysqli_num_rows($result);

if($num_rows >0){
echo "You've already registered! Thanks anyway";
exit;
}

else{

mysqli_query($db_connection, "INSERT INTO Users (Name, Email) VALUES('$name' , '$email')");
echo "Thanks for signing up, $name.";
}

Use http://php.net/manual/en/mysqli.error.php on your query.
I.e.: or die(mysqli_error($db_connection))

Warning

Your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements.

Footnotes:

If you want to avoid duplicates, set your column(s) as UNIQUE.

Test for duplicates in a Mysql database?

1 Answers1