Duplicate Key Violation Error 23000

[Landslyde]

In the beginning, I had just one unique key set up in a table: username. When a username duplication in the Registration form was entered, I'd catch it and handle it like this:

catch (PDOException $ex){
    if ($ex->getCode() == 23000){ //Check if it's a duplciate key violation.
        $unameErr = '* Username already in use';
} 

Today, in the same table, I added another unique key: email. Now that I have two unique keys that will both have the same violation, how can I tell which one was the duplicate so I can provide the correct error to the user?

[Barand]

With MySQL the error message will tell you which key. What RDBMS are you using?

[Landslyde]

With MySQL the error message will tell you which key. What RDBMS are you using?

MySQL

 

How do I get it to show me the key? Sorry, but I've never handled this particular kind of error before. Your insight wld be appreciated. Thanks, Barand.

[Barand]

try getting the error message (or error info) as well as the error code

[Landslyde]

try getting the error message (or error info) as well as the error code

 

Frustrating. I know which key I'm violating but don't have the smarts to assimilate it in the Try/Catch block. I've looked high and low on the web and can't find even one example on this.

 

The error:

* Username already in useexception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'cowboy@dfwit.co' for key 'email'' in /var/www/html/dfwit/register.php:36 Stack trace: #0 /var/www/html/dfwit/register.php(36): PDOStatement->execute(Array) #1 {main}

As you can see, it's the email duplication that throws the exception, even giving me the key 'email' to assist me in coding the exception. I just can't get that part right. I can't figure out how to code it. I appreciate any help you can offer, Barand. Thank you.

Edited April 11, 2015 by Landslyde

[Landslyde]

try getting the error message (or error info) as well as the error code

** Hehehe...sorry abt the double post. I failed to see the error output had the two words running together, hit rewind and edited it and got two posts. Cheesy   **

 

Frustrating. I know which key I'm violating but don't have the smarts to catch it in the Try/Catch block. I've looked high and low on the web and can't find even one example on this.

 

The error:

* Username already in use: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'cowboy@dfwit.co' for key 'email'' in /var/www/html/dfwit/register.php:36 Stack trace: #0 /var/www/html/dfwit/register.php(36): PDOStatement->execute(Array) #1 {main}

As you can see, it's the email duplication that throws the exception, even giving me the key 'email' to assist me in coding the exception. I just can't get that part right. I can't figure out how to code it. I appreciate any help you can offer, Barand. Thank you.

Edited April 11, 2015 by Landslyde

[maxxd]

This may not be the most efficient solution, but it worked in the couple easy tests I just ran on it:

$err = "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'cowboy@dfwit.co' for key 'username'' in /var/www/html/dfwit/register.php:36 Stack trace: #0 /var/www/html/dfwit/register.php(36): PDOStatement->execute(Array) #1 {main}";
$colMarkStart = "' for key '";
$colMarkEnd = "' in /var/";

$startKey = strpos($err, $colMarkStart) + strlen($colMarkStart); //the first occurrence of the first character in the search string plus the length of the search string
$endKey = (strpos($err, $colMarkEnd) - 1) - $startKey; //-1 from the end string position to account for the end quote, then subtract the starting position to get the desired span of characters
$column = substr($err, $startKey, $endKey); //pull that span of characters from the error string

print("<p>Duplicate column : {$column}</p>");

[Landslyde]

Thanks, maxxd. I actually came up with a solution on another forum that, while much like your suggestion, does what I need it to do.

catch (PDOException $ex){
    if ($ex->getCode() == 23000) { //Check if it's a duplciate key violation.
        if (preg_match("/email/", $ex)) {
            $emailErr = '* Email address already in use';    
        } elseif (preg_match("/username/", $ex)) {
            $unameErr = '* Username already in use';
        } else {
            throw $ex;  //Rethrow other errors
            echo '<script type="text/javascript">alert("There was an unforeseen database error."+"\\n"+"The database administrator has been contacted."+"\\n"+"\\n"+"Please try again later. Thank you"); </script>';
            mail_error($ex);
        }
}  

As you can see in the error I posted, it lets me know what key threw the error: either email or username, the two Unique columns of this table. And this little bit of preg_match code keys in on those two words and forks the correct error msg to the user. And I'm rolling again

Thanks for your input, maxxd.

[Barand]

Note that doing it that way only works for your table with username and email as unique keys and will require separate code for each table with unique keys, whereas maxxd's solution will work with any table

[maxxd]

Barand, that's what I was thinking with that solution - if you add additional keys to the unique constraint (phone number, for example), the script will handle it automatically.

 

Either way, glad you got your solution Landslyde!