prevent duplicate inserts

carrietc

Hi guys,
I've seen this type of question many many times but I can't seem to figure it out for my own.

I have a form that inserts records into a database. This works fine. I want to add a check to prevent duplicate emails and if there's a duplicate email it goes to 1 page, and if its unique then it goes to another page.

I want to learn so if someone could please point me in the right direction that would be great. I saw code similar to SELECT etc but I just dont know where to put it

Here's my code from the w3schools tutorial:

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

mysql_select_db("****", $con);

$sql="INSERT INTO customers (FirstName, LastName, Email, Address, City, State, ZipCode)
VALUES
('$_POST[FirstName]','$_POST[LastName]','$_POST[Email]','$_POST[Address]','$_POST[City]','$_POST[State]','$_POST[ZipCode]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

dagon

i would make email an unique key, then check the results of the insert to determine what page to redirect to

carrietc

dagon;10975931 wrote:
i would make email an unique key, then check the results of the insert to determine what page to redirect to

Ok thanks! I made the email a unique key. Now how do I check the results to determine what page to redirect to?

dagon

mysql_affected_rows() if its 1, it was inserted so not a duplicate

carrietc

dagon;10975933 wrote:
mysql_affected_rows() if its 1, it was inserted so not a duplicate

Ok thank you for that too.. I've implemented the string you gave me but I'm definitely not doing it properly. Now it's deleting the duplicate and then adding it again because I have the INSERT string under it. I'm assuming I need to use If Else statements, just not sure how...

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

mysql_select_db("", $con);

mysql_query("DELETE FROM ** WHERE customers id = '$Email'");
$rc = mysql_affected_rows();

echo "Records deleted: " . $rc;

$sql="INSERT INTO customers (FirstName, LastName, Email, Address, City, State, ZipCode)
VALUES
('$_POST[FirstName]','$_POST[LastName]','$_POST[Email]','$_POST[Address]','$_POST[City]','$_POST[State]','$_POST[ZipCode]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

dagon

why are you deleting in the first place?

carrietc

dagon;10975942 wrote:
why are you deleting in the first place?

Wouldn't this delete the attempted duplicate entry? Or do I have that wrong?

dagon

drop the DELETE, keep the INSERT, use mysql_affected_rows() after insert to determine if a row was inserted, which will tell you if there was a duplicate email address.

carrietc

dagon;10975953 wrote:
drop the DELETE, keep the INSERT, use mysql_affected_rows() after insert to determine if a row was inserted, which will tell you if there was a duplicate email address.

ha makes sense now... I'll do this at the office in the morning. Thank you for all your help!!!

Carrie