I'm sorry if this questions has been asked before, but I couldn't find an answer for quite a while.
The problem is when trying to insert a row in a MySQL table that has multiple UNIQUE keys. If we take the following table as a reference:
----------------------------------------
| id | username | email |
----------------------------------------
| 1 | myuser | test@test.com |
----------------------------------------
Both username and email are UNIQUE keys. If I run a query to insert new row with username = 'myuser' I will get an 1062 error for duplicate entry and some text in the message saying that key with id 3 is duplicated. Something like this:
array(3) {
[0]=>
string(5) "23000"
[1]=>
int(1062)
[2]=>
string(34) "Duplicate entry 'myuser' for key 3"
}
Is there a way in PDO to get the name of the duplicated column, so that I can show to the user which field he needs to change because of this error?
Because the cases are that both fields can be duplicated, or just the first/second one and I need to tell the user which field exactly is.
I thought to parse the text message and hard code the ID for the keys, but someone can change the keys in the database tomorrow, drop a key or add a new one and that Key Number does not seem like a reliable source of information.
Or maybe the best option is to SELECT all the values that are keys to check them before INSERT?
Thank you!
No, there is no way to get a better error message with information of what table that is failing. The message you see in the array "Duplicate entry 'myuser' for key 3" is almost the information you want. The number 3 is actually the name of the index that you've made. You could change to a more meaningful, like "username", and get a better error message which you could forward to the user. If you want to make custom error messages you have to extract the information you want out of that message.
Agree, there is no way to get a useful report about UNIQUE error. Truly best solution is call SELECT before INSERT and process result by self way. It will have an impact on performance but most of applications INSERT less often, so you might get away with it.