Preventing duplicate DB entries

[Arc]

Hello, I have a progam that keeps track of addresses. The client can add addresses 1 at a time or enter entire lists at a time.

I am wondering what is the best/fastest/most proficient way to check If that email address is already in the db and if so not add it.

For example I have all of these email addresses in an array $EmailAddys[]

Now I do a for each loop to add them to the db like so

$NumItems=Count($EmailAddys);
For ($Counter=0; $Counter<$NumItems; $Counter++){
$Sql = "INSERT INTO tblEmailInfo (EmailAddress, ListName,Active) VALUES ('$EmailAddys[$Counter]','$ListName','TRUE')";
$Result = mysql_query($Sql,$Connect);
}

Now, Should I do individual Select statements before adding each address like so

$NumItems=Count($EmailAddys);
For ($Counter=0; $Counter<$NumItems; $Counter++){
$Sql = "SELECT * FROM tblEmailInfo WHERE EmailAddress = '$EmailAddys[$Counter]'";
$Result = mysql_query($Sql,$Connect);
$NumRows=mysql_num_rows($Result);
If($NumRows >0){
$Sql = "INSERT INTO tblEmailInfo (EmailAddress, ListName,Active) VALUES ('$EmailAddys[$Counter]','$ListName','TRUE')";
$Result = mysql_query($Sql,$Connect);
}
}

Or should I do a select statement and get every email address and put them in an array and then compare the 2 arrays and remove all the duplicates from my new addresses array that im about to add? If so then what is the best way to do that?

Thanks!😃

[Pig]

The easiest way would be to simply loop through each new entry that is trying to be posted to the database, and then run a quick query for each to see if it is already in there. Something like:
$result = mysql_query("SELECT email FROM table WHERE email='" . $_POST['email'] . "'");
Then if you get any results then echo out an error and move on to the next one, otherwise insert it.
if( mysql_num_rows($result) > 0 ) echo("That email is already in our database<br>.");
else $result = mysql_query("SOMETHING HERE TO INSERT THE NEW ADDRESS");

[Arc]

Hmm yah, but isn't it actually alot slower to do 2 SQL statements for each entry as apposed to comparing arrays then only doing 1 SQL statement for each new entry?

I am talking about 1000's of entries here, so speed is definately a factor.

[drawmack]

the most efficient way it so make email address a unique id. Then the dbms will not let you add duplicates.

ALTER TABLE users ADD UNIQUE INDEX email (email)

or something like that.

make sure you error trap in the program so that the mysql warnings are not output to the screen.

[Arc]

Thanks Draw that's a good idea but then I realized the table can have duplicate email addresses as long as the Listname is different.

For example

[e-mail],NewList
[e-mail],OldList

that would be ok but

[e-mail],NewList
[e-mail],NewList

that would not.

Hmm I guess I am going to have to go with the checking the db with each and every entry thing.
🙁

[ahundiak]

Just tweak drawmack's suggestion to create an unique index using both fields.

ALTER TABLE users ADD UNIQUE INDEX email (listname,email)

Unique indexes are the only way to ensure your data stays unique especially in a multi-user enviroment.

[Arc]

That would allow me to add the same email address twice as long as the listname was different?

Hmm... Seems to me that would just make sure there were no dupolicate email addresses or listnames.

[drawmack]

I would make sure you didn't have a duplicate index.

This index is the two column pair so you can enter duplicate usernames and duplicate emails but not a duplicate set.