How to insert only if there is no duplicate already?

Question

I want to insert a new user only, if there is no user with the same email already in the database.

I was reading this post: MySQL - ignore insert error: duplicate entry but the problem there is that after the execution of the sql statement, i don't know if the new user was successful inserted or not.

Answer 1

after the execution of the sql statement, i don't know if it was successful or not

If I understand correctly, you want to know whether an insertion occurred or not after using INSERT IGNORE. After executing your query call LAST_INSERT_ID() and if it returns 0 it means a row was not inserted.

https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

Test

mysql> insert ignore into example (`id`, `data`) VALUES (2,'hello');
Query OK, 1 row affected (0.04 sec)

mysql> insert ignore into example (`id`, `data`) VALUES (3,'hello');
Query OK, 1 row affected (0.06 sec)

mysql> insert ignore into example (`id`, `data`) VALUES (2,'hello');
Query OK, 0 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

Warning

Some versions of MySQL have a bug where last_insert_id() doesn't work correctly with INSERT IGNORE: http://bugs.mysql.com/bug.php?id=67535

Answer 2

Just do a regular insert, then check the error code afterwards:

$result = mysql_query("INSERT ... stuff that causes duplicate key error");
if (mysql_errno() == 1022) {
   ... account already exists ...
}

This is somewhat safer than doing a "select" first to see if the email exists, then inserting if it doesn't. Another parallel request might "steal" the account out from under you.

The mysql error codes are all documented here: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

---

comment followup:

My answer MAY be more reliable in some cases, e.g. consider this:

mysql> create table foo (x int primary key auto_increment, y  text);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (y) values ('bar1'); // id #1
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (y) values ('bar2'); // id #2
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (x,y) values (2, 'bar2'); // try to create a dupe id #2
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Note how you STILL get a last_insert_id(). That's because it's always the value of the last SUCCESSFUL insertion. The value will not get reset just because an insert failed.

If you're doing multiple inserts with your DB handle, and try to use the if last_insert_id == 0 trick, then you may get a false answer, because you'd be getting the ID of some OTHER insert that succeeded, not this one that just failed.

And note that insert ignore doesn't help either:

mysql> insert ignore into foo (x,y) values (1, 'bar1'); // create dupe id #1
Query OK, 0 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Answer 3

if you use PDO it's simple:

try{
   // INSERT new account
}
catch(PDOException $ex){ /* already exist -> errorInfo */ }

it work if you have:

• UQ key for your email field
• attr mode as $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

that's all

---

added:

just in case for a proper understanding - see comment:

Any DB-Layer, for example:

class TableExample
{
    ...    
    // sample with inner logic
    public function createUser(...)
    {
        ...
        $this->db->query("INSERT INTO ...");
        ...
    }

    // sample with the AR etc.
    public function updateFooBar(...)
    {
        ...
        $user->name  = 'name';
        $user->email = 'email';
        $user->save();
        ...
    }    
}

Some X-layer:

class ModelStuff
{
    ....
    public function foo()
    {
        try{
            $this->example->createUser();
            ...
            $this->example->updateFooBar();
            ...
        }
        catch(PDOException $ex){
            // TODO: e.g. ($ex->getCode() == 23000)? ...
            // all PDOException with TableExample catched here
        }
    }
}

etc.

Compiled all my comment:

• PDOException already aggregate the errorInfo (corresponds to PDO::errorInfo) For accees you can simple use $ex->getCode(); //SQLSTATE - 23000, which occurs for a duplicate-key error
• • Use the errorInfo for more detail about error e.g.: $ex->errorInfo[1] - where 1 is a Driver specific error code
• be careful with variant LAST_INSERT_ID()