Jo Smo Jo Smo - 12 days ago 4
MySQL Question

How to insert only if there is no duplicate already?

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

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)
Comments