Handle error for duplicate entries - PHP MySQL

问题:

I have a PHP form which enters data into my MySQL database. My primary key is one of the user-entered values. When the user enters a value that already exists in the table, the MySQL error "Duplicate entry 'entered value' for key 1" is returned. Instead of that error, I would like to alert the user that they need to enter a different value. Just an echoed message or something. I guess my question comes down to: how to turn a specific MySQL error into a PHP message Thanks

edit: nickf's answer below is nice, but is there any way to discern between specific errors?

回答1:

To check for this specific error, you need to find the error code. It is 1062 for duplicate key. Then use the result from errno() to compare with:

mysql_query('INSERT INTO ...');
if (mysql_errno() == 1062) {
    print 'no way!';
}

A note on programming style
You should always seek to avoid the use of magic numbers (I know, I was the one to introduce it in this answer). Instead, you could assign the known error code (1062) to a constant (e.g. MYSQL_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

回答2:

You can check the return value from mysql_query when you do the insert.

$result = mysql_query("INSERT INTO mytable VALUES ('dupe')");

if (!$result) {
    echo "Enter a different value";
} else {
    echo "Save successful.";
}

回答3:

With mysql_error() function http://php.net/manual/en/function.mysql-error.php

回答4:

Use mysql_errno() function, it returns the error numbers. The error number for duplicate keys is 1062. for example

$query = mysql_query("INSERT INTO table_name SET ...);
if (mysql_errno() == 1062){
    echo 'Duplicate key';
}

回答5:

try this code to handle duplicate entries and show echo message:

  $query = "INSERT INTO ".$table_name." ".$insertdata;
                if(mysqli_query($conn,$query)){
                    echo "data inserted into DB<br>";                   
                }else{
                   if(mysqli_errno($conn) == 1062)
                       echo "duplicate entry no need to insert into DB<br>";
                   else
                    echo "db insertion error:".$query."<br>";

                }//else end