Handling duplicate entry errors?

[MythicFrost]

Hi,

I've got a DB set up with an e-mail and user column that's unique.
That way if anyone enters a username or e-mail identical to someone else I can handle it and say "that username or email is already taken".

At the moment I'm just getting the output (error) from this code:

if (!mysql_query($sql,$con)) {
    die('Error: ' . mysql_error());
}

which is: Error: Duplicate entry 'myusername-myusername@anywhere.com' for key 'user'
Any thoughts on how to do this?

Kind Regards

 

[Cerebrus' Maw]

Hi,

I've got a DB set up with an e-mail and user column that's unique.
That way if anyone enters a username or e-mail identical to someone else I can handle it and say "that username or email is already taken".

At the moment I'm just getting the output (error) from this code:

if (!mysql_query($sql,$con)) {
    die('Error: ' . mysql_error());
}

which is: Error: Duplicate entry 'myusername-myusername@anywhere.com' for key 'user'
Any thoughts on how to do this?

Kind Regards

Ok, not sure if I understand the premise of the question, but I'm assuming that you want to output the message
"That username or email address is already taken." ?
In that case, a very simple modification of your code would be:

if (!mysql_query($sql,$con)) {
    die('That username or email address is already taken');
}

Now, the die function will quit your script. You may not want that to happen, so instead you would save the error message to a variable and use it later, so like:

if (!mysql_query($sql,$con)) {
    $error_msg = 'That username or email address is already taken';
}

and test that variable later on to make it some what more graceful.

The only drawback here is that the SQL error might not necessarily be a duplication error, so you may be better off:
A) A more generic error message (There was an error with your query)
B) Handling the Error Number which is returned from PHP, and changing the error message as such.

Hope this helps, -Maw

 

[designguy79]

Why not run a query that first looks for that username, and if it is indeed taken, return the correct error message? There are plenty of other reasons your other query may fail, so assuming it is because of a duplicate entry is not good practice.

Something like... SELECT username FROM table WHERE username="incoming_user_name_to_test"

Sorry, I am too lazy to write better PHP or SQL.

 

[CANEHDN]

I agree with DesignGuy. I would check to see if the record already exists before running the insert query.

 

[Cerebrus' Maw]

The only thing I would point out about using the select statement is that you then have to run two SQL queries, the Select and the Insert. Which wont really matter on smaller tables but nonetheless...

 

[CANEHDN]

The only thing I would point out about using the select statement is that you then have to run two SQL queries, the Select and the Insert. Which wont really matter on smaller tables but nonetheless...

As long as you're indexing the fields you'd be okay. The select would find out if it's a dup or not. If the insert fails then there was an error with the sql. Since the connection errors would be found during the db connection. I can do this at work on millions of records and it's extremely fast.