CODEWITHSUNDEEP
phpmysqlerror-handling
RobHardgood
RobHardgood

Reputation: 303

How to handle error for duplicate entries?

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?

Upvotes: 30

Views: 79767

Answers (7)

Evan TOder
Evan TOder

Reputation: 91

  INSERT **IGNORE** into sales(id,order_date,amount)
  values(1, '2021-01-01', 250)

the IGNORE parameter stops generating and error but the duplicate entry is still not allowed.

$affectedRows = mysqli_stmt_affected_rows($stmt);
$newRowId = mysqli_insert_id($dbConn);

To error check If duplcate entry without php or mysql raising error ...

  /* -------------------------------------------------------
    the function that will securely place the new data
    into the database table
  ------------------------------------------------------- */
function insertIntoDatabase($dbConn, $tablename, $userData) 
{
  /*
    Generate placeholders for prepared statement 
    [$userData] is associate array ie    "firstname" => "henry"
    with the part left of => being the exact database column
    name and the part right of => being value inserted in column
  */
  $placeholders = rtrim(str_repeat('?, ', count($userData)), ', ');
  // Generate comma-separated list of column names
  $columns = implode(', ', array_keys($userData));
  // Construct the SQL query for insertion
  $sql = "INSERT IGNORE INTO $tablename ($columns) VALUES ($placeholders)";
  // Prepare the SQL statement
  $stmt = mysqli_prepare($dbConn, $sql);
  // Bind parameters to the prepared statement
  mysqli_stmt_bind_param($stmt, str_repeat('s', count($userData)), ...array_values($userData));
  // Execute the prepared statement to insert data into the database
  mysqli_stmt_execute($stmt);

   /*
    feedback on success or faliure
    if [$affectedRows] = 0 and ][$newRowId] = 0
    then we know nothing was added to database
   */
  $affectedRows = mysqli_stmt_affected_rows($stmt);
  $newRowId = mysqli_insert_id($dbConn);

  return [$affectedRows, $newRowId];
}

refer to https://vb6code.com/code-php-insert-data-into-mysql-database.php

Upvotes: 1

Spinstaz
Spinstaz

Reputation: 331

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());

  }
}

Upvotes: 0

jensgram
jensgram

Reputation: 31508

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 :)

Upvotes: 55

Hassan Saeed
Hassan Saeed

Reputation: 7080

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

Upvotes: 1

Kyad
Kyad

Reputation: 129

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

Upvotes: 0

nickf
nickf

Reputation: 545985

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

Upvotes: 5

Codler
Codler

Reputation: 11256

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

Upvotes: 0

Related Questions