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:
- Before running the INSERT, run a
SELECT TRUE FROM ``users`` WHERE ``username`` = "john_galt";to see if the username already exists in the database, but I feel like I added a unique constraint to the field so that I wouldn't have to do this -- kinda defeats the purpose otherwise. -
strpos($sql->error, 'Duplicate entry') === 0-- seems horribly hackish.
Is there a better method for determining this using PHP's MySQLi class?
