Handle error for duplicate entries - PHP/MySQL

Question

What is the proper way to handle error on duplicate entries for PHP/MySQL?

The code below is not working even tho the code 1062 is the right code for duplicate entries. Should i use DBO instead here? Unfortunately i'm not familiar yet.

<?php 
try {
    mysql_query('INSERT INTO TP2_ORGANISME VALUES (A0A0, Equiterre, 1, 
                        /photos/equiterre_logo.png, Steve Guilbault, [email protected]');
} catch {
    if (mysql_errno() == 1062) 
      echo 'Duplicate key entry';
} 
?>

I'm just looking for a simple try catch or anything that can just let me print a message to the user who entered a duplicate that he has to enter another value for the primary key.

thanks

Answer 1

mysql_query() does not throw exceptions (and only exceptions can be caught). mysqli_ and PDO can, if you enable it to. mysql_ is also very obsolete (and has been for many years already), so use PDO or MySQLi with exception-mode enabled, and it'll work. Both these APIs support prepared statements, so you should use that too if you start inputting variables in your query.

For MySQLi, you need mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); before the connection, and for PDO you need $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

MySQLi example

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($hostname, $username, $password, $databasename);
$mysqli->set_charset("utf8");

try {
    $mysqli->query("INSERT INTO TP2_ORGANISME 
                               VALUES ('A0A0', 'Equiterre', 1, '/photos/equiterre_logo.png', 'Steve Guilbault', '[email protected]')");
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() == 1062) {
        // Duplicate user
    } else {
        throw $e;// in case it's any other error
    }

}

PDO example

$pdo = new PDO("mysql:host=$hostname;dbname=$databasename;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Enables exception mode

try {
    $pdo->query("INSERT INTO TP2_ORGANISME 
                               VALUES ('A0A0', 'Equiterre', 1, '/photos/equiterre_logo.png', 'Steve Guilbault', '[email protected]')");
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Duplicate user
    } else {
        throw $e;// in case it's any other error
    }
}

• MySQLi exception class and mysqli_report()
• PDO exception class and PDO::setAttribute()

When you start introducing variables into your queries, look into using a prepared statement (How can I prevent SQL injection in PHP?).

Answer 2

I found this block of code really helped capture the 1062 error, and then redirect from the signup page to the login page, if the error happened. Tested it, and it all works.

catch (PDOException $e) {

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

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

}