How to handle error for duplicate entries?

Question

I have a PHP form that 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.

How to turn a specific MySQL error into a PHP message?

score 54 · Accepted Answer · edited Dec 11 '19 at 09:06

54

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:

mysqli_query('INSERT INTO ...');
if (mysqli_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. MYSQLI_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 :)

edited Dec 11 '19 at 09:06

Utkarsh

537
9
16

answered Jun 30 '10 at 06:47

jensgram

31,109
6
81
98

Yeah, I just found that too :D I plugged it in and it worked perfectly. Thanks jensgram, just the solution I was looking for! – RobHardgood Jun 30 '10 at 07:13
@jensgram: +1 thanks for very useful answer & tip. I'm just adding this link for those using PDO: http://www.php.net/manual/en/pdo.errorcode.php – CodeVirtuoso Sep 22 '11 at 08:09
1

For OOP it's **`$mysqli->errno`**. – i336_ Jan 17 '17 at 11:22
to find out error number: echo mysqli_errno($link); //where link is your database connection. – Clark Superman Jul 26 '20 at 14:49
Why do you spend a while paragraph on instructing the reader to use constants, instead of just (or at least in addition to) applying it to your code snippet? – m7913d Dec 25 '21 at 11:46

score 4 · Answer 2 · answered Jun 30 '10 at 06:11

4

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.";
}

answered Jun 30 '10 at 06:11

nickf

537,072
198
649
721

Aha, i've seen this before, and this definitely looks right. thanks nickf! – RobHardgood Jun 30 '10 at 06:29
Actually, is there any way to check if it's the specific error? – RobHardgood Jun 30 '10 at 06:33
Yep - `mysql_errno()` will return the error code of the last error. Off the top of my head, I don't know what that code is, but you could just check for that inside the `(!$result)` branch. – nickf Jun 30 '10 at 07:57
The OOP equivalent of `mysql_errno()` is **`$mysqli->errno`**. – i336_ Jan 17 '17 at 11:22

score 1 · Answer 3 · answered Aug 13 '17 at 16:54

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

score 0 · Answer 4 · answered Jun 30 '10 at 06:11

0

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

answered Jun 30 '10 at 06:11

Codler

10,951
6
52
65

this just turns on error reporting, as far as i can tell. thanks anyway. – RobHardgood Jun 30 '10 at 06:30

score 0 · Answer 5 · answered Jun 21 '17 at 07:39

0

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

answered Jun 21 '17 at 07:39

Kyad

119
3
13

score 0 · Answer 6 · answered Oct 15 '21 at 12:04

This is my full code that I used and works perfect. Its PDO friendly, and can handle your error easily, (once you have used die to discover what that is. Then you can copy the error message from there, and enclose it in an if. This came from a signup page, where I wanted to redirect to the login page, if the primary key (email) was found, and produced an error.

function insertUserDetails($email, $conn){

  try {
      $query = $conn->prepare ("INSERT INTO users (emailaddress) VALUES (:email)");
            $query ->bindValue('email', $email);
            $query->execute();
        }
  catch (PDOException $e) {

      if(str_contains($e, '1062 Duplicate entry')) {
          header("Location: login.php");

      }
          die("Error inserting user details into database: " .  $e->getMessage());

  }
}

How to handle error for duplicate entries?

6 Answers6

Linked

Related