prevent duplicate entries

[lukeurtnowski]

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...

$emailcheck = "select email from users where email = ".$email."";
if (mysql_num_rows != 1) {
echo "error";
} else {
//insert statement
} 

Is this ok?

[guido2004]

Put a unique index on email, and then just do the insert and check for a duplicate key error.

[K. Wolfe]

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?

[felgall]

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.

[K. Wolfe]

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)

[K. Wolfe]

Part of my user model I started working on yesterday, unfinished of course.

<?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;
        }
    }
}

[felgall]

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.

[guido2004]

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?

[K. Wolfe]

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.

[lukeurtnowski]

back to my question, is this ok

    $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."; }