SitePoint Sponsor

User Tag List

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

    Check If Already In Database

    Hi,

    I have created a membership script but I have omitted one crucial factor. The script doesn't check if the email address is already in the database. Can anyone advise how I check if the an input from a field is already in the database?

    (I kept entering the same email address whilst testing)

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

    I've now got this which reads if the email address is already in the DB but it doesn't stop the email address from being inserted twice. Do I put some sort of stop code in there?


    Code:
    $query = mysql_query("SELECT * FROM users WHERE email = '". $registerEmail ."' OR email = '". $email ."'"); 
    
    if (mysql_num_rows($query) > 0) 
    { 
         echo 'Email Address is Already In Use.'; 
    }

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Add a unique index on the email column. Then there's no need to do a select to check the existence of the email address. Just use INSERT IGNORE if you want to ignore the duplicate key error, or do an INSERT and then check the returning error code to handle the duplicate key error.

  4. #4
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So if I change the key to unique and keep the existing message that will then prevent the email being added to the database and return a message.

    Is that right?

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

    How do I also change the table for email to unique? Im sure Ive seen this but cant seem to find it now.

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

    I changed the field to unique but it returns this horrible message and blocks out the page.

    Duplicate entry 'someone@yahoo.com' for key 'email'

    How can I change the code I had to block the insert of duplicate emails?

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    In my opinion that is validation that should occur at the application level rather than the database level. However, if you would like to base it on whether MySQL returns an error or not you would simply check the error code. I have never done it this way myself so had to look up the error code. Searching on MySQL revealed that the error code for a duplicate key entry is 1062. Therefore, you would use something like the below to check and carry out the appropriate action when a duplicate is attempted.

    Code PHP:
    if(mysql_errorno() == 1062) {
       // alert user of duplicate entry
    }

    Normally when dealing with forms there is some type of validation layer prior to saving data to MySQL. That is where I would carry this out. If you are just dealing with spaghetti code though it probably doesn't make much of a difference either way. It goes without saying though I would always define a unique key in this case. Perhaps even make the email column the primary key of the table depending on context/purpose of the table.
    The only code I hate more than my own is everyone else's.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by justlukeyou View Post
    I changed the field to unique but it returns this horrible message and blocks out the page.
    it cannot create the unique index as long as the table actually contains duplicates

    remove those first, and create the index again

    i disagree with oddz, i would do a single call to the database to insert a new row (and detect whether there was a duplicate error), rather than doing two calls to the database for each insertion (a select first, followed by the insert)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    i disagree with oddz, i would do a single call to the database to insert a new row (and detect whether there was a duplicate error), rather than doing two calls to the database for each insertion (a select first, followed by the insert)
    Hi,

    I have this code which checks if the email address is already in the database however it doesn't stop the email from being inserted. So its only doing half the job. Can you advise how I can stop the email from being entered?


    Code:
    $query = mysql_query("SELECT * FROM users WHERE email = '". $registerEmail ."' OR email = '". $email ."'"); 
    
    if (mysql_num_rows($query) > 0) 
    { 
         echo 'Email Address is Already In Use.'; 
    }

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Kill the script by using exit in the conditional. That is simplest but least user friendly way of doing so. Ideally you would want to show some type of message to the user telling them that the email already exists.
    The only code I hate more than my own is everyone else's.

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

    I do want to show that the email already exists. Is it possible to kill the script after the message is displayed?


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
  •