CODEWITHSUNDEEP
mysqlduplicate-data
Eric_ph
Eric_ph

Reputation: 128

MySQL error: Duplicate entry '[email protected] ' for key 3

This is my table

enter image description here

And this is my php query code

// Check for unique username
    $quser = "SELECT user_id FROM users
        WHERE (username = '$u' AND email = 'e')
            ";

    $ruser = mysqli_query($dbc, $quser) or die("MySQL error: " .   mysqli_error($dbc) . "<hr>\nQuery: $quser");

if(mysqli_num_rows($ruser) == 0 ){

//do the INSERT INTO query
$q = " INSERT INTO users (lang_id, username, pass, email, active, registration_date)
               VALUES ( '$l','$u', SHA1('$p'), '$e', '$a', NOW() )
             ";

}else{

echo 'That email address has already been registered.';
}

When I test it by entering the duplicate email address into the HTML form (assumed that the form validation is good), it returns error as shown below:

MySQL error: Duplicate entry '[email protected] ' for key 3
---------------------------------------------------------
Query: SELECT user_id FROM users WHERE (username = 'dsfdsf' AND email = 'e')

And of course, the value is NOT entered into the table yet.

These are the solutions I have been trying:

1/ I removed the unique indexes for username and email, data gets inserted, but still duplicated, which i NEVER expect.

2/ I tried duplicate username, but not email, the same error returns.

The question in my head now is why does it NOT returns my customized error message as in }else{ part, but the one generated by server or so?

Can you help? Thanks

Upvotes: 0

Views: 556

Answers (2)

Aamir
Aamir

Reputation: 326

$ruser = mysqli_query($dbc, " SELECT * user_id FROM users 
WHERE username like '$u' or  email like '$e' ");

in the select Query Email should be $e and not just e

try

$res=mysql_query("select * from users 
where username like '$u' or email like '$e' ");

if(mysql_num_rows($res)==0)
mysql_query(" insert into users
(lang_id, username, pass, email, active,registration_date) values
('$l','$u','$p','$e','$a',NOW()) ");

else
echo "Duplicate entry";

This may be the Error
Oh the problem is that you have user_id in your table as primary and it should be not be null , even user_level cannot be NULL, hence everytime you insert a new row you should insert a value for user_id and user_level

mysql_query(" insert into users
(lang_id, username, pass, email, active,registration_date,user_id,user_level) 
values('$l','$u','$p','$e','$a',NOW(),$userid,'$user_level') ");

hope this solves your problem

Upvotes: 0

frlan
frlan

Reputation: 7260

Chaging your request to 

$quser = "SELECT user_id FROM users
        WHERE username = '$u' 
           or email = '$e'
            ";

should do the trick. You missed a $ at email as well as this query only returned values if both are set... so only duplicate email or duplicate username wouldn't be catched.

Maybe you can catch these kind of errors with some exception handling to avoid bad luck on timing (2 processes are inserting data at the same time).

Upvotes: 1

Related Questions