Use $errno to catch INSERT repeat primary key error (Error number 1062)

When using MySQL databases in PHP, you may encounter some common errors when inserting data. One of them is a primary key conflict error, that is, when you try to insert an existing primary key value, MySQL will return an error, usually error number 1062.

When using mysqli extension, we can catch these errors via mysqli::$errno . The mysqli::$errno attribute stores the error number returned by the last MySQL query. Through this property, we can easily detect primary key conflicts and handle them accordingly.

Here is a simple example showing how to capture duplicate primary key errors (Error number 1062) when inserting data:

Sample code:

 <?php
// Database connection configuration
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "my_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check if the connection is successful
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert data SQL Statement
$sql = "INSERT INTO users (id, username) VALUES (1, 'new_user')";

// implement SQL Query
if ($conn->query($sql) === TRUE) {
    echo "New record insertion successfully";
} else {
    // Check if it is a duplicate primary key error（Error number 1062）
    if ($conn->errno == 1062) {
        echo "mistake: Duplicate primary key value。";
    } else {
        echo "mistake: " . $conn->error;
    }
}

// Close the connection
$conn->close();
?>

Code parsing:

1. Database connection : First, we use new mysqli() to create a database connection. We provide the host name, user name, password and database name of the database. If the connection fails, the program will exit with an error message.

2. SQL Insert Statement : We wrote a simple insert statement that inserts a new record into the users table. Assume that the id column is the primary key and the id value attempted to insert is 1 .

3. Execute SQL query : Perform SQL insertion operation through $conn->query($sql) . If the insertion is successful, the return value is TRUE and the prompt message for the insertion is successful.

4. Catch error : If the insertion fails (such as primary key conflict), $conn->errno will return an error number. In this example, we check if $conn->errno is 1062 , which is the error number for repeated primary key errors in MySQL. If it is a duplicate primary key error, we output a specific error message: "Dull primary key value". If it is another type of error, we will display the error message returned by MySQL.

5. Close connection : After the operation is completed, we use $conn->close() to close the database connection and release the resources.

summary:

By using mysqli::$errno we can easily catch and handle MySQL errors, especially common duplicate primary key errors (Error number 1062). This method helps improve the robustness of the program and avoids program crashes or errors caused by duplicate primary keys.

If you want to further improve the processing logic, you can take more appropriate actions when catching duplicate primary key errors, such as skipping insertions, updating existing records, or returning more detailed error messages.