Prevent Duplicate Entries Into Database

[justlukeyou]

Hi,

I have a piece of code which checks if an email address is already in the database. However what ever I try I cant get it to prevent a duplicate entry from entering the database.

Is there a piece of code which stops entries into a database if there are duplicates?

    $query = mysql_query("SELECT * FROM users WHERE email  = '". $email ."'"); 
$emailduplicate = null;
if (mysql_num_rows($query) > 0) 
{ 
$emailduplicate = 'Email Address is Already in Use.  Please <a href="http://www.website.com/test/activation.php?userid=Y">Retrieve Your Password</a>.'; 
}       

          if 
          ($registerPassword === $email)  {
            $errors['emailalreadyinuse'] = 'You cannot join.';             
        }   
        
        
        
if(!$errors){
$registerPassword = md5($registerPassword);
$query = "INSERT INTO users (usertype, usertypelink, activationcode, firstname, surname, email, password, logo, date_registered) VALUES ('" . $usertype . "', '" . $usertypelink . "', '" . ($activationcode) . "', '" . $firstname . "', '" . $surname . "', '" . mysql_real_escape_string($registerEmail) . "', '". $registerPassword ."', '". $logo ."', NOW())";


                         
     $result = mysql_query($query); // remove the or die(mysql_error()) code after you resolve the error
     if($result){
          $success['register'] = '
          
          
          Thank you for registering with website.com.<br>
          You will soon receive a confirmation email. Please click the confirmation link.<br><br>'; 

[Pullo]

Hi,

what are you checking here:

$registerPassword === $email

What is $registerPassword in this context?

[Cups]

If the email field in your database is deemed to be unique, ie only one entry is permitted for each email address then the best way to enforce this is to make that field UNIQUE.

Here is some info on the Mysql UNIQUE index also check the mysql manual page.

Then upon failure to add an email address because the UNIQUE index has been triggered, you will find your database will issue an error number, in mysql this is number 1062. The appearance of this number should be caught and the appropriate warning issued (Duplicate Found).

Detecting errno 1062.

So, you turn your code upside down. In pseudocode that would look like:

Go ahead and try and insert the data into the database.

check for an errno

if( errno && errno === 1062)
   Display Duplicate Found message
else
  Something else went wrong message

[Lemon Juice]

Detecting errno 1062

Personally, I prefer to use a SELECT statement to check if the entry already exists in the database because detecting mysql errno seems a bit hackish to me and is non-standard. Also, what will you do if you have more than 1 UNIQUE columns? In order to find out which index has been violated you'd have to parse the error message Duplicate entry 'x@example.com' for key 'email_key_name' - not elegant at all. And what if more than 1 indexes have been violated in the same INSERT and you'd want to know all violations to present all validation errors to the user? Impossible, because mysql will report only one violation and won't even try the others.

Unless this is a piece of code that is run very often and squeezing out all performance is essential I always use a separate SELECT for this purpose. Adding a user to the database is hardly ever an action that is executed often (often in this case meaning several times per second).

[Cups]

You listed some good reasons not to use that method, thanks for elaborating -- sounds as if you've fallen foul of this method, I will try and remember to add this warning in future.

Still, a UNIQUE index on email would at least stop this:

However what ever I try I cant get it to prevent a duplicate entry from entering the database.

As the conditional check is on there being an $errors array, I'd suggest something like this:

if (mysql_num_rows($query) > 0) 
{ 
$emailduplicate = 'Email Address is Already in Use.  Please <a href="http://www.website.com/test/activation.php?userid=Y">Retrieve Your Password</a>.'; 
$errors['duplicatefound'] = true;
} 

This thread perhaps turns up the old LAMP chestnut "which rules should I tell my database to handle, and how much should be handled by my logic?".

[Lemon Juice]

To clarify - I haven't completely fallen foul of this method, I always use UNIQUE indexes in such cases, and wherever I can create a necessary constraint in the db I do it. It's only the validation checking that I find cumbersome by catching insert errors. In this particular case I'd use a SELECT for validation and I might also catch the 1062 error for the extremely rare case when the SELECT validation result changes in the split second before the INSERT. Or, I might lock the table for the whole operation - but for such rare events as registering new users I simply don't bother.

[Cups]

Still, a UNIQUE index on email would at least stop this:

Sorry, I did not mean it to sound as if you did not know where/when to use it. I added that comment for the sake of the OP, or anyone else listening.

[Lemon Juice]

Okay, no problem, it never hurts to explain stuff too much for anyone who might need it

[justlukeyou]

Hi,

I have found this a bit confusing. I selected the unique option within MySQL and this now stops duplicate email addresses from being added to the database. Is there anything else I use.

Should I being adding anything to the code within the PHP script?

[Lemon Juice]

Hi,

I have found this a bit confusing. I selected the unique option within MySQL and this now stops duplicate email addresses from being added to the database. Is there anything else I use.

Should I being adding anything to the code within the PHP script?

The unique index will prevent duplicate entries. Our small discussion was about how to check that a user is trying to use an existing email address. Cups suggested that with a unique index you don't have to use a SELECT to check for an existing email like you did at the beginning of your code - you could simply INSERT straight away and then check if the query returned an error - if mysql_errno is 1062 then you display 'Email Address is Already in Use...'. My preference is to use a SELECT for checking like you did, just because I find this method more elegant and flexible. It looks like everything is okay with your code.

[etidd]

Does this SELECT technique work with a PDO connection? This doesn't seem to be working for some reason.

$dupeQuery = mysql_query("SELECT * FROM users WHERE email  = '" . $email . "'");
                if (mysql_num_rows($dupeQuery) > 0){
                    return 3;
                } else
                    return 1; 

My apologies if I'm not supposed to post to a 3-month-old thread.

[Pullo]

Hi etidd,

My apologies if I'm not supposed to post to a 3-month-old thread.

Although nothing speaks against this, if you don't get an answer to your question here, it might be a good idea to start a new thread, phrasing your question concisely in the title.

[etidd]

Hi Dave,

I started a thread about this yesterday when I got far enough to see that I am catching the duplicate e-mail using the technique discussed here, but the script I'm using is not printing the error message for the user to see just like it does for all the other errors I catch, like the user not entering in a name.