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, steve@equiterre.org');
} 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

**WARNING**: Do not use the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface which was removed in PHP 7. A replacement like [PDO is not hard to learn](https://phpdelusions.net/pdo) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Here parameters are **NOT** [properly escaped](http://bobby-tables.com/php) and this has severe [SQL injection bugs](http://bobby-tables.com/) in this code. Escape **any** and all user data, especially from `$_POST` or `$_GET`. — tadman, Jul 18 '19 at 05:58
This code can't possibly work. I'm not sure where you found it, but I'm expecting the resource you're using to learn how to use MySQL in PHP is way too old to be trusted. `mysql_query` is no longer available, and besides, you can't insert data like that. You'll need to work through a basic SQL primer before any of this is going to make much sense, or use a database abstraction layer like e [RedBeanPHP](https://redbeanphp.com/), [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/master/eloquent) for a softer start. — tadman, Jul 18 '19 at 06:00

score 6 · Answer 1 · edited Apr 28 '22 at 16:50

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', 'steve@equiterre.org')");
} 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', 'steve@equiterre.org')");
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Duplicate user
    } else {
        throw $e;// in case it's any other error
    }
}

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

Thanks, but i'm not sure what to do this with line : $pdo = new PDO("mysql:host=$hostname;dbname=$databasename;charset=utf8", $username, $password); - Where can i find the hostname and/or database name, honestly im using a VM and i'm kind of confused right now. — user9939473, Jul 18 '19 at 05:58
That creates the connection, similar to how you have `mysql_connect()` today. You should remove all instances using the `mysql_` functions, and use PDO or MySQLi instead. — Qirel, Jul 18 '19 at 05:59
On a VM you'll often start with `localhost` and `root`, but you'll want to lock that down and **be absolutely certain that remote users can't connect**, especially if your root account has no password. — tadman, Jul 18 '19 at 06:01
okay, do i need to get the mysql extension ? It says Fatal error: Call to undefined function mysqli_report() — user9939473, Jul 18 '19 at 06:21

score -2 · Answer 2 · answered Oct 15 '21 at 11:54

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

}

Handle error for duplicate entries - PHP/MySQL

2 Answers2