MySQLi Error 1062 (dealing with multiple duplicate key entries)

[terungwa]

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.While parsing the error code, I have also notice that, if you have multiple unique fields as duplicates, only the first duplicate encountered will be returned in the message. This is not very helpful to the end user.

 

Is there any way to parse the returned error code to reflect all duplicate fields, please see sample code below?

$error=array();
$sql = 'INSERT INTO staff(username, email, phone)
VALUES (?, ?, ?)';
$stmt = $conn->stmt_init();
$stmt = $conn->prepare($sql);
// bind parameters and insert the details into the database
$stmt->bind_param('sss', $username, $email, $phone);
$stmt->execute();
if ($stmt->errno == 1062) {
$errors[] = "One of the fields is already in use.";
}

 

[mac_gyver]

only the first duplicate encountered will be returned in the message.

 

 

that's because the query fails at the first duplicate key value encountered and doesn't attempt to process any of the other values.

 

to do what you are asking will require that you to run a SELECT query first to see if any matching data already exists.

 

you could also only insert the first unique field, with null values for the others, then run update queries to add the other unique field(s), one at at time.

[terungwa]

to do what you are asking will require that you to run a SELECT query first to see if any matching data already exists.

 

Thank you mac_gyver, I already had three queries each checking for the existence of each of the unique entries. I was thinking their might be a way to reduce the processing overhead in one swoop.

[Psycho]

Thank you mac_gyver, I already had three queries each checking for the existence of each of the unique entries. I was thinking their might be a way to reduce the processing overhead in one swoop.

You mean three queries as in one for each field? If so, then, yes, that can be improved. You can run one select query to get the data as to which fields are duplicated or not.

SELECT SUM(username = ?) as usernameDupe
       SUM(email = ?) as emailDupe
       SUM(phone = ?) as phoneDupe
 
FROM staff
WHERE username= ?
   OR email = ?
   OR phone = ?

Of course you would need to bind two sets of the values to the query. You could remove the WHERE clause and only have to bind one set of values. You would get the same results, but I think it would be inefficient with large datasets.

SELECT SUM(username = ?) as usernameDupe
       SUM(email = ?) as emailDupe
       SUM(phone = ?) as phoneDupe
 
FROM staff

You will get one record in the result set with three fields: usernameDupe, emailDupe & phoneDupe. The value for each field will be 0 (no duplicates) or >= 1 (there are dupes). The value should only ever be 0 or 1. But, it could be greater than 1 if any dupes already exist in any field for the tested value.

Edited November 11, 2014 by Psycho