[RESOLVED] Two Unique keys with MySQL 23000 Error in Try/Catch block

[Landslyde]

I have a table with two Unique fields: username & email. My registration form prevents a user from registering an already-registered username and email address. My problem is I don't know how to distinguish which threw the error when the Try/Catch block catches this. Using this:

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

I get this:

* Username already in use: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[e-mail]' 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}

That tells me the 'email' key violated and threw the error. But I have no clue how to use that in the Try/Catch block to differentiate between the two possible keys. This is my latest try:

catch (PDOException $ex){
    		if (($ex->getCode() == 23000) && (key(array &$username))) { //Check if it's a duplciate key violation.
        	$unameErr = '* Username already in use: '.$ex;
        } elseif (($ex->getcode() == 23000) && (key(array &$email))) {
        	$emailErr = '* Email address already in use: '.$ex;
    	}

but that leaves me with a syntax error. I've smoked down the Google servers on this one with nothing to show for it. If you know how to code this, I'd appreciate your insight. Many thanks.

[NogDog]

Well, your syntax error is in how you're calling key(), which should just be...

key($someArrayHere)

That being said, it's not clear to me why you're using key() there, and what those $email and $username variables would be at that point (assuming they're both arrays), and why you'd want to know what key/index is currently being pointed to in those arrays?

[Landslyde]

NogDog, the tangle you see is my feeble attempt at trying to sort out how to distinguish which Unique field threw the error: username or email. I'm using 'key' because the error tells me that the key 'email' threw the error, so that's why I'm using what you see. I think it shld be obvious that I'm completely lost in how to code this though. I have absolutely no idea how to tell which Unique field threw the error so I can send that back to the user to let them know that they either entered an already-registered username and/or an already-registered email address. And that's all I'm trying to do. Seems simple enough. Sure, I suppose I cld use two insert statements, testing username in one and email in the other. But it seems to me that I shld be able to handle both of those in one Try/Catch block. I just don't know how. And that's why I'm here: to learn from those who might know. Then I'll be able to pass that along to others too. But I have to know how to do it first. So hopefully someone here will be able to help me.

[NogDog]

Okay, so it looks like once you get into the catch, $ex (possibly after being cast to a string) includes this text:

for key 'email'

So you could look for that pattern with something like

preg_match('#for key \'([^\']+)\'#', (string)$ex, $matches);

If that finds a match, it will return true, and $matches[1] will contain the text found between the single quotes (hopefully 'email' or 'username' -- or whatever the column names actually are.

[Weedpacket]

If you don't want to be dependent on the exact phrasing of the error message, you could look up the two values to see which one finds a match (try the username first, as I suspect that will be the more likely offender).

And, for a bit of clarification, [man]key[/man] refers to the keys of a PHP array; it has nothing to do with index keys in a DBMS.

Incidentally [man]PDOException[/man] error codes are strings, not integers: leaving the quotes off the [font=monospace]'23000'[/font] means an extra cast is necessary to make the comparison. Many error codes use characters other than 0-9 so to test for them you'd need the quotes; for consistency they should be used here, too.

[Landslyde]

Hey NogDog: Thanks for your reply. Are you sure that's the way to go? Let me explain again to make sure we're on the same page.

A user fills out the Registration form and submits it with the various entries, two of which are a username and an email address. I then move to add their entered info to a members table, where two of the fields of that table are Unique, meaning that MySQL will throw a 23000 exception error is the username or email address is already in the members table.

When it throws this 23000 exception error, it will tell me whether it was the username key or the email key. Now this is where I get lost. The 23000 error shows me what threw it, the username or email keys, and you're telling me that using preg_match is how to solve my issue?

The error itself tells me whether it was the username or email address trying to be duplicated. So I don't understand what preg_match is going to tell me that the error failed to.

If you view the thrown error in one of my above posts, you'll see that it knows which field (email in this case) the error was thrown on. The exception handling has already rooted out that problem for me. But since there are two Unique keys in the table, I would like to know how to use that error information to determine which message to send back to the user, either on their username entry or their email entry. And I just can't see where preg_match is the way to do this. This really has me confused, NogDog. Please remeber, friend, I'm new to the PHP world. And you may very well be right as rain here. I just can't see the logic. This is where you come in again 🙂

[Weedpacket]

The error message says what column failed. But to use that information for anything the message has to be read and understood. You can read and understand the message, but your problem is that you need your program to do the same. [man]preg_match[/man] can do enough reading and understanding of the error message for it to do that. In this case, NogDog's code will result in the [font=monospace]$matches[/font] array containing an element saying [font=monospace]email[/font] or [font=monospace]username[/font], and you can use that to proceed as appropriate.

[Landslyde]

Weedpacket:

I appreciate your explanation on NogDog's suggestion. Seems that preg_match is the ticket home on this one afterall. Thanks again to both of you.

[Weedpacket]

Only downside would be if MySQL's developers changes the phrasing of the message, or if the database's user speaks a different language or has otherwise customised its error messages.

[Landslyde]

I'm studying the preg_match function now. Yes, I can surely see the downside of phrase matching. But it seems this is the only solution to my problem. Well, the best of two anyway. As long as I can gleen 'email' and 'username' from the error msg, then I'm okay with it. At least I can make the conditional stmt fork the right way to alert the user. Many thanks again for your explanation. It helped me understand what NogDog was trying to tell me. Peace out...

[Landslyde]

		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);
    			}
			}
		}

Works like a charm 🙂

[Derokorian]

From the manual:

Tip
Do not use preg_match() if you only want to check if one string is contained in another string. Use strpos() or strstr() instead as they will be faster.

The way your using it, you're better off using [man]strstr[/man] or [man]strpos[/man] to check for email or username.

[Landslyde]

Derokorian:

I appreciate the "tip". Definitely worth looking into. Thanks.