How can I elegantly catch the column name(s) a 1062 error code (duplicate key entry) occurred on?
When I do:
$db = @new mysqli('localhost', 'root', '******', 'database');
$pstmt = $db->prepare("INSERT INTO users (person_id, user_name, password, e_mail) VALUES (LAST_INSERT_ID(), ?, ?, ?)");
$pstmt->bind_param("sss", $_POST["register_user_name"], md5($_POST["register_password"]), $_POST["register_e_mail"]);
// error occurred
if (!$pstmt->execute()) {
if ($db->errno == 1062) {
$column_duplicate_key_entry = /*how to obtain the column name(s)?*/;
}
}
Unique columns are user_name and e_mail
I know I could get the error string by calling $db->error and parsing the fields by myself. But that's not elegant in my opinion. Is there any other better solution?
What I want to do is to try to insert a new user. If the inserted user name already exists an 1062 error is thrown, so it is when the e-mail address already exists. I want to check if only the user name is taken, or only the e-mail address, or maybe both. To say it again, I want to do it on a elegant way without to select the e-mail and user name first and to check if they already exist. If possible I would like to do ONE insert query and get all information out of it.
MySQL returns an error in the form of a number, a state, and a message. Without parsing the message you will not be able to determine what column is duplicated.
Furthermore, MySQL will bomb on the first failure. So, if you have user_name and e_mail as duplicates, only the first duplicate encountered will be returned in the message.