Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts

    How to check if an email address already exists in the database?

    Hi. I am designing a user registration page. I allow only 1 membership per email address, so when a new member is signing up, if they provide an email address that is already in the database, it will give them an error. I already have everything set up, I just can't for the life of me figure out what to put in the function.

    I messed around for a while and couldn't figure it out. I already have the function set up. Currently it just looks like this but obviously it's a stub for now:

    Code:
    function isEmailAlreadyInDatabase($email) {
      //This is a stub  
      if(email has been found)
      {
        return true;
      }
      else
      {
        return false;
      }
      
    }
    Thanks in advance for all help!!

  • #2
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Maybe something like this:




    PHP Code:
    $new_email$_POST['email'];

    $sql mysql_query("SELECT email FROM member_list WHERE email = $new_email");
    $row mysql_fetch_row($sql);

    if(!isset(
    $row[email])){
        
    // no match .... continue.
    } else {
       
    // match , back to registration page.

    Might be syntax errors.



    ------
    Last edited by Len Whistler; 01-17-2010 at 09:09 AM.
    Leonard Whistler

  • Users who have thanked Len Whistler for this post:

    skcin7 (01-18-2010)

  • #3
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    PHP Code:
    function isEmailAlreadyInDatabase$email )
    {
        
    $res mysql_query'SELECT `id` FROM `user` WHERE `user_email` = \'' mysql_real_escape_string$email ) . '\' LIMIT 1' );
        return ( bool ) 
    mysql_num_rows$res );

    Last edited by kbluhm; 01-17-2010 at 06:28 PM.

  • Users who have thanked kbluhm for this post:

    skcin7 (01-18-2010)

  • #4
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?

    Code:
    function isEmailAlreadyInDatabase($email) {
    
      $sql = mysql_query("SELECT * FROM LUM_User WHERE Email = $email");
    
      if($sql)
      {
        return true;
      }
      else
      {
        return false;
      }
      
    }
    A few notes:
    - The table is called LUM_User
    - The field is called Email (capitalized)

    I'm a novice at this and thanks for your help!

  • #5
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by skcin7 View Post
    Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?

    Code:
    function isEmailAlreadyInDatabase($email) {
    
      $sql = mysql_query("SELECT * FROM LUM_User WHERE Email = $email");
    
      if($sql)
      {
        return true;
      }
      else
      {
        return false;
      }
      
    }
    A few notes:
    - The table is called LUM_User
    - The field is called Email (capitalized)

    I'm a novice at this and thanks for your help!
    PHP Code:
    function isEmailAlreadyInDatabase($email) {
      
    $query "SELECT * FROM LUM_User WHERE Email = '".$email."'";
      print 
    $query// check if query is valid and comment if is
      
    $sql mysql_query($query);
      if(
    $sql && mysql_num_rows($sql) == 1){
        return 
    true// there is a single mail
      
    }
      return 
    false// invalid query, no mail address or more then one

    best regards

  • Users who have thanked oesxyl for this post:

    skcin7 (01-18-2010)

  • #6
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    These are still not working! What the hell am I doing wrong?

    EDIT: I got it working! ALthough, I feel as if I am not doing it in the most efficient method. I basically use a while loop to loop through each row, and I check to see if the email is the same on each row, like this:

    Code:
    function isEmailAlreadyInDatabase($email) {
    
      //Include connection settings
      include 'includes/settings.php';
      //Select database
      mysql_select_db("nichol1_admin", $con);
    
    
      $query = "SELECT * FROM LUM_User";
      $sql = mysql_query($query);
      
      while ($row = mysql_fetch_array($sql))
      {
        $email1 = $row['Email'];
        
        if($email1 == $email)
        {
          return true;
        }
        
      }
      
      return false;
    }
    Last edited by skcin7; 01-17-2010 at 03:45 PM.

  • #7
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    IMHO, the best way to do this is to enforce a UNIQUE constraint on the field (which you should be doing regardless) and then go ahead and perform a blind INSERT statement. If the INSERT fails due to the constraint, then ask the user to input a different email (or block them altogether, whatever logic you want to enforce). Check against the MySQL error #1062 using mysql_error().

    The next best step is to fetch a COUNT of any matching record that matches the input value and then return the count value. If the count value is 1, the email exists and you can enforce logic on that case (of course if the count value is greater than 1 you have a bigger problem).

    Retrieving any (other) data, certainly retrieving all data and looping through it until you find a match is very inefficient.

  • Users who have thanked bdl for this post:

    skcin7 (01-18-2010)

  • #8
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Quote Originally Posted by skcin7 View Post
    Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?
    That surprises me. I have used a set of similar calls with that type of query in the past with no issues.

    Another set of calls I have used with success would be similar to:
    PHP Code:
    function isEmailAlreadyInDatabase$email )
    {
        
    $res mysql_query'SELECT COUNT( `id` ) AS `count` FROM `user` WHERE `user_email` = \'' mysql_real_escape_string$email ) . '\' LIMIT 1' );
        
    $row mysql_fetch_assoc$res );
        return ( bool ) ( int ) 
    $row['count'];

    Last edited by kbluhm; 01-17-2010 at 06:27 PM.

  • Users who have thanked kbluhm for this post:

    skcin7 (01-18-2010)

  • #9
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    There is an easier way of doing it, quick and simple. But remember you have to change these examples to match yours. Meaning change the table name, field name, etc to your database.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • Users who have thanked masterofollies for this post:

    skcin7 (01-18-2010)

  • #10
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    @kluhm> Both of your code segments offer a better way than most; however, you're returning a value cast as a BOOL, which may not give the results you expect, based on whether or not the actual return value can be neatly processed as a boolean.

  • Users who have thanked bdl for this post:

    skcin7 (01-18-2010)

  • #11
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Casting it to boolean doesn't [shouldn't] affect the desired result. If the query's returned value is 0, it will be cast as FALSE, reflecting that the email does not currently exist. Any other numeric value is cast to TRUE, meaning it exists.
    Last edited by kbluhm; 01-17-2010 at 06:29 PM.

  • Users who have thanked kbluhm for this post:

    skcin7 (01-18-2010)

  • #12
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    I notice that none of the code except the OP's "inefficient" way of doing it has no mysql_select_db() in them, where as that one does. Do you actually do that outside of the function when you connect to the database? You should be doing both at the start of your script really

    EDIT: and also the connection setting - Is it that you've not connected even before this point?
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    skcin7 (01-18-2010)

  • #13
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Code:
    function isEmailAlreadyInDatabase($email)
    {
        //Include connection settings
        include 'includes/settings.php';
        //Select database
        mysql_select_db("[insert the correct name here]", $con);
    
        $sql = mysql_query('SELECT 1 FROM member_list WHERE LOWER(email)=\''.mysql_real_escape_string(strtolower($email)).'\'');
      
        return ((mysql_num_rows($sql)) ? true : false);
    }
    Last edited by MattF; 01-17-2010 at 08:42 PM.

  • Users who have thanked MattF for this post:

    skcin7 (01-18-2010)

  • #14
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by JAY6390 View Post
    I notice that none of the code except the OP's "inefficient" way of doing it has no mysql_select_db() in them, where as that one does. Do you actually do that outside of the function when you connect to the database? You should be doing both at the start of your script really

    EDIT: and also the connection setting - Is it that you've not connected even before this point?
    I think the reason why it wasn't working at first is because it didn't have mysql_select_db() in it. For some reason it will only work if I add the connection settings and select the database INSIDE the function, even though I already have this code outside of the function at the top of the page.

  • #15
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by bdl View Post
    IMHO, the best way to do this is to enforce a UNIQUE constraint on the field (which you should be doing regardless) and then go ahead and perform a blind INSERT statement. If the INSERT fails due to the constraint, then ask the user to input a different email (or block them altogether, whatever logic you want to enforce). Check against the MySQL error #1062 using mysql_error().
    I think based on what you described this sounds like a very good way of doing it. How do you enforce a UNIQUE constraint on a field? I'm using phpMyAdmin to handle the database but I can't seem to find a way to add UNIQUE to a field.

    EDIT: I figured out to alter the table and make a field unique you need to execute some code. So, I executed this SQL:

    Code:
    ALTER TABLE `LUM_User` ADD UNIQUE (
    `Email`
    )
    I got a message saying that the SQL statement executed, so the Email field is now has the UNIQUE constraint applied right? I can't seem to find in phpMyAdmin any indication of this.


    Quote Originally Posted by bdl View Post
    Retrieving any (other) data, certainly retrieving all data and looping through it until you find a match is very inefficient.
    I agree, the way I have it set up now is very inefficient :-( (although at least for the time being it works)
    Last edited by skcin7; 01-18-2010 at 04:57 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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