Duplicate Key Error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Duplicate Key Error

Post by tecktalkcm0391 »

Can you make it so if a MySQL database returns a duplicate key error, it displays "Sorry but this e-mail has already been used" instead of "Duplicate entry 'example@example.com' for key 2"

Thanks
User avatar
harrisonad
Forum Contributor
Posts: 288
Joined: Fri Oct 15, 2004 4:58 am
Location: Philippines
Contact:

Post by harrisonad »

simple suggestion:
- I suggest that you must first disable the error reporting on php.ini
- Trap the mysql error and Identify it
- If it matches the DUPLICATE error, spit your customzed message
rllqph
Forum Newbie
Posts: 2
Joined: Mon May 29, 2006 3:27 am

Post by rllqph »

what i normally do is this...

- perform a query that look for matched entry in the db and user input.
e.g. SELECT email FROM account WHERE email = $inputemail

- if there's a result, then spit out the customize error message.

- if no result, then it means that the email address is not present in the db.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The problem with 'verifying if there is already such an id' is that between the verification and the insertion there is a gap (doesn't matter how big it is, it is there). This gap can lead to problems (eg: another user is registered between the verification and insertion) so you would need to use at least transactions to make these two actions look like one, atomic, action.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Can anyone tell me how do to what rllqph was taking about?
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Here's how I would do it.

Code: Select all

$result = @mysql_query($query_that_might_have_unique_key_violation);
if (mysql_errno() == 23000) { //that's the error number for duplicate unique (I think)
  // duplicate email
}
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

thank you
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

finally used it and for anyone else it is 1062 not 23000
Post Reply