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

WARNING: Do not use the obsolete mysql_query interface which was removed in PHP 7. A replacement like PDO is not hard to learn and a guide like PHP The Right Way helps explain best practices. Here parameters are NOT properly escaped and this has severe SQL injection bugs in this code. Escape any and all user data, especially from $_POST or $_GET. — tadman
– tadman, Commented Jul 18, 2019 at 5: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, Doctrine, Propel or Eloquent for a softer start. — tadman
– tadman, Commented Jul 18, 2019 at 6:00

Your Common Sense · Accepted Answer · 2022-04-28 16:50:33Z

7

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

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

edited Apr 28, 2022 at 16:50

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Jul 18, 2019 at 5:42

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user9939473 Over a year ago

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.

Qirel Over a year ago

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.

tadman Over a year ago

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.

user9939473 Over a year ago

okay, do i need to get the mysql extension ? It says Fatal error: Call to undefined function mysqli_report()

Qirel Over a year ago

Yes, you need the mysql and mysqli/PDO extensions.

Spinstaz · Accepted Answer · 2021-10-15 11:54:31Z

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

}

answered Oct 15, 2021 at 11:54

Spinstaz

3477 silver badges12 bronze badges

Collectives™ on Stack Overflow

Handle error for duplicate entries - PHP/MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related