SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,644
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    prevent duplicate entries

    I have a mysql table to hold my members. I want to prevent people from writing to the database using the same email address. so im thinking my php code would be something along this...
    PHP Code:
    $emailcheck "select email from users where email = ".$email."";
    if (
    mysql_num_rows != 1) {
    echo 
    "error";
    } else {
    //insert statement

    Is this ok?
    "Oh, and Jenkins--apparently your mother died this morning."

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Put a unique index on email, and then just do the insert and check for a duplicate key error.

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Both of the above. You want to have a function to be able to return a value of true or false for account creation, and perhaps down the road an ajax function to check before submission?

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,819
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    select before insert is double handling unless you need to use the values rethred from the select before doing the insert - simply specifying fields in the database that need to be unique as unique will allow you to detect that the entry already exists when you try to insert it without having to select it first.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by felgall View Post
    select before insert is double handling unless you need to use the values rethred from the select before doing the insert - simply specifying fields in the database that need to be unique as unique will allow you to detect that the entry already exists when you try to insert it without having to select it first.
    I would think the small amount of overhead created from a quick select on an indexed column, and a non-common feature such as registration is acceptable for a feature that can be used to save the user time, as well as page requests (speaking of an ajax request of course)

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Part of my user model I started working on yesterday, unfinished of course.

    Code PHP:
    <?php
     
    class Application_Model_DbTable_Users extends Zend_Db_Table_Abstract
    {
     
        protected $_name = 'tbl_users';
     
        public function authUser($usr, $pass)
        {
            $hash = md5($pass . "||" . $usr);
            $row = $this->fetchRow("email_address = '$usr' AND user_password = '$hash'");
            $data = array(
                'last_login' => date("Y-m-d H:i:s")
            );
            $this->update($data, "email_address = '$usr'");
            if(count($row) > 0) 
            {
                //return user info to append to session
                return $row['user_id'];
            }
        }
     
        public function checkAvail($usr)
        //seperated for AJAX use later
        {
            $row = $this->fetchRow("email_address = '$usr'");
            if(count($row) == 0) {
                return true;
            }
        }
     
        public function createAccount($usr, $pass)
        {
            if($this->checkAvail($usr) == true)
            {
                $hash = md5($pass . "||" . $usr);
                $data = array(
                    'email_address' => $usr,
                    'user_password' => $hash
                );
                return $this->insert($data);
            } else {
                return false;
            }
        }
    }

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,819
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I would think the small amount of overhead created from a quick select on an indexed column, and a non-common feature such as registration is acceptable for a feature that can be used to save the user time, as well as page requests (speaking of an ajax request of course)
    But it doesn't save the user time.

    Doing a select first and then an insert means that all the entries which are NOT duplicates have two database accesses while the rare duplication has one.

    Doing an insert with a duplicate key check means that all the non-duplicates have just the one database access needed and only the rare duplicate might need to do a second call to select the associated data from the existing record.

    Assuming that 1% of entries result in a duplicate then for 1000 people using the select would require 1990 database calls while usingjust the insert with a duplicate key check followed by a select on the duplicates woul require 1010 database calls - a significant saving (which would be even higher when you consider that far fewer than 1% are likely to be duplicates).


    Only if you expect duplicate errors over half the time does doing the select first make sense.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    And if you put a unique index on the column, MySQL takes care of not inserting duplicates. If you don't then you have to handle that in your code, and if you forget or make a mistake, your table will contain duplicate values. Why would you want to do stuff yourself when you can "delegate" it?

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    I was stating both, not just through server side code. I don't see anything wrong with using the SQL to return a user taken message, but I'm suggesting that you create a function that does only the check for later use.

  10. #10
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,644
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    back to my question, is this ok
    PHP Code:
        $sql "insert into users1 (`username`, `password`, `session_id`, `email`, `regdate`, `regip`, `active`, `isAdmin`) values ('$user', '$pass', '$session', '$email', '$date', '$ip', 1, 0)";
        
    $result mysql_query($sql); 
    if (!
    $result) {     echo "Enter a different value"; } else {     echo "Save successful."; } 
    "Oh, and Jenkins--apparently your mother died this morning."

  11. #11
    Non-Member
    Join Date
    Sep 2013
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lukeurtnowski View Post
    back to my question, is this ok
    PHP Code:
        $sql "insert into users1 (`username`, `password`, `session_id`, `email`, `regdate`, `regip`, `active`, `isAdmin`) values ('$user', '$pass', '$session', '$email', '$date', '$ip', 1, 0)";
        
    $result mysql_query($sql); 
    if (!
    $result) {     echo "Enter a different value"; } else {     echo "Save successful."; } 
    This code is correct,no error found in this code.


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
  •