SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Prevent Duplicate Entries Into Database

    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?

    PHP Code:
        $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>'

    Last edited by SpacePhoenix; Dec 27, 2012 at 01:16. Reason: placed php tags around php code

  2. #2
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,313
    Mentioned
    178 Post(s)
    Tagged
    8 Thread(s)
    Hi,

    what are you checking here:

    Code PHP:
    $registerPassword === $email

    What is $registerPassword in this context?

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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:

    Code:
    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

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    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).

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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:
    PHP Code:
    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?".

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by 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.

  8. #8
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Okay, no problem, it never hurts to explain stuff too much for anyone who might need it

  9. #9
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by justlukeyou View Post
    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.

  11. #11
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    623
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Does this SELECT technique work with a PDO connection? This doesn't seem to be working for some reason.

    PHP Code:
    $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.

  12. #12
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,313
    Mentioned
    178 Post(s)
    Tagged
    8 Thread(s)
    Hi etidd,

    Quote Originally Posted by etidd View Post
    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.

  13. #13
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    623
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •