Use $errno to automate the testing of different SQL failure types

When using MySQL databases in PHP, you may encounter an error when executing SQL queries. In order to facilitate positioning these errors, the error code provided by mysqli ::$errno can be used to determine the cause of the failure and handle it accordingly. This article will introduce how to automatically identify and test various types of SQL execution failures with mysqli::$errno .

1. Understand mysqli::$errno

mysqli::$errno is an attribute containing the error code of the recently executed MySQL query. It returns an integer value that represents different types of errors. This is very useful for developers and can help us locate issues when executing SQL.

Usually, mysqli::$errno and mysqli::$error are used together, the latter will return the error message when an error occurs, while the former provides the specific error code for the error. Through this information, SQL errors can be handled accordingly.

2. Common MySQL error codes

Here are several common MySQL error code examples and their meanings:

When you execute SQL queries, you can check these error codes through mysqli::$errno and process them accordingly accordingly.

3. Use mysqli::$errno to handle errors

First, we need an instance that connects to the MySQL database, and then execute a SQL query and check if there is an error. Here is a sample code:

 <?php
$mysqli = new mysqli("localhost", "user", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query = "SELECT * FROM non_existent_table"; // Intentionally written wrong table name
$result = $mysqli->query($query);

if (!$result) {
    // If the query fails，Get error code and error information
    echo "Error code: " . $mysqli->errno . "<br>";
    echo "error message: " . $mysqli->error . "<br>";

    // 根据Error code做不同的处理
    switch ($mysqli->errno) {
        case 1045:
            echo "No permission to access，Please check the permissions of the database user。";
            break;
        case 1064:
            echo "SQL Syntax error，Please check the query syntax。";
            break;
        case 1146:
            echo "The table does not exist，Please check the table name。";
            break;
        case 1054:
            echo "The column does not exist，Please check the column name。";
            break;
        case 1062:
            echo "Repeat entries，Violation of unique constraints。";
            break;
        default:
            echo "Other errors，Please check。";
    }
} else {
    // Query successful，Processing results
    while ($row = $result->fetch_assoc()) {
        echo "data: " . $row['column_name'] . "<br>";
    }
}

$mysqli->close();
?>

In the above code, if SQL execution fails, the error code and error message will be printed first. Then, based on the value of mysqli::$errno , we judge the error type through the switch statement and provide specific error prompts.

4. Use mysqli::$errno for error testing

You can test the effect of mysqli::$errno by intentionally introducing errors. For example, you can try the following common errors:

• Error table name: When the query table does not exist, MySQL will return error code 1146 (the table does not exist).

• Error SQL Syntax: Syntax errors usually return 1064 error code.

• Permission problem: If the database user does not have sufficient permissions, the error code 1045 is returned.

During testing, you can modify the SQL query statement to trigger these errors and obtain the corresponding error code through mysqli::$errno .

5. Example: SQL query that failed with URL test

In some complex SQL queries, it may involve using URLs or external data sources. If external resources are involved in the query, we can use a method similar to the following to check whether the URL is valid: