PHP MySQLi Error Handling 1062 Duplicate Key Entry

Question

How can I elegantly catch the names of the columns (s), error code 1062 (duplicate record) occurred?

When I do this:

$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: user_name and e_mail

I know that I can get an error string by calling $db->error and $db->error fields myself. But this is not elegant, in my opinion. Is there an even better solution?

I want to do to try to insert a new user. If the inserted username already exists, error 1062 occurs, so this is when the email address already exists. I want to check if only a username or only an email address is accepted, or perhaps both. To repeat this, I want to do it in an elegant way, before I first select the email name and username and check if they already exist. If possible, I would like to make ONE insert request and get all the information from it.

Answer 1

MySQL returns an error in the form of a number, state, and message. Without parsing the message, you cannot determine which column is duplicated.

In addition, MySQL will bomb on the first crash. So, if you have user_name and e_mail as duplicates, only the first duplicate will be returned in the message.