CODEWITHSUNDEEP
phpmysqlmysqliunique-constraint
M Miller
M Miller

Reputation: 5642

PHP / MySQLi - Catch unique ID conflict

I'm wondering if there's an easy way to check if an INSERT statement triggers a conflict due to a unique index on a MySQL field while performing the INSERT.

For example, say I'm adding a user to a database, and username is a unique index:

$sql = new MySQLi(...);
$res = $sql->query('INSERT INTO `users` (`username`) VALUES ("john_galt");');

A user named john_galt already exists, so the INSERT won't be performed. But how can I best respond to the user with a meaningful error (i.e. A user with that username already exists; please choose a unique name.)?

I thought of a couple things, like checking if insert_id is set -- which it shouldn't be if the query generated an error. But that error could be anything.

Similarly, I could check to see if $sql->error is not empty, except, again, that could be anything as well -- a syntax error, or a malformatted value, or whatever. Obviously I'm not going to print out the actual MySQL error for the end user.

The two solutions I can think of are:

Is there a better method for determining this using PHP's MySQLi class?

Upvotes: 3

Views: 3522

Answers (1)

StathisG
StathisG

Reputation: 1169

You can use errno to get the returned error code and use that, for example:

if($sql->errno === 1062) {
    //do something
}

Here's a list of error codes for MySQL 5.5: http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

Upvotes: 8

Related Questions