How to accurately locate errors through $errno when using mysqli::query

In PHP, the mysqli extension provides rich database interaction functions, which can help developers to efficiently connect and operate with MySQL databases. The mysqli::query method is usually used to execute database queries, but during query execution, you may encounter various errors. In order to improve the efficiency of error troubleshooting, the errors when used with mysqli ::Query can be accurately located through the mysqli ::$errno attribute.

This article will introduce how to use mysqli::$errno to catch errors and provide detailed error information to help developers locate problems more quickly.

1. Understand mysqli::query and mysqli::$errno

The mysqli::query method is used to execute an SQL query statement, return query results, or return true or false when performing INSERT , UPDATE , DELETE and other operations to modify the database.

The mysqli::$errno attribute contains the error code of the last database operation. Through this property, we can understand whether an error occurred when executing SQL statements, and even obtain the specific error type.

Here are the basic usages of mysqli::query :

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

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

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if (!$result) {
    echo "Query failed with error code: " . $mysqli->errno;
} else {
    // Process query results
}
?>

In the above example, we execute the SQL query statement through mysqli::query and use mysqli::$errno to obtain the error code when the query fails.

2. How to locate errors through mysqli::$errno

By checking the mysqli::$errno property, we can get the error code about the failure of the database operation. Based on the error code, we can further understand the problem. For example, common error codes include:

• 1049 : Unknown database.

• 1146 : The table does not exist.

• 1064 : SQL syntax error.

Example: Accurate positioning errors

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

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

// Wrong SQL Statement，Syntax error
$sql = "SELEC * FROM users"; // Notice，I wrote it inadvertently here `SELECT`
$result = $mysqli->query($sql);

if (!$result) {
    // Get error code and error information
    echo "Query failed with error code: " . $mysqli->errno . " - " . $mysqli->error;
} else {
    // Process query results
}
?>

In the example above, we deliberately wrote an incorrect SQL statement ( SELEC * FROM users ). When the query execution fails, we get the error code through mysqli::$errno and get the error information through mysqli::$error . This can help us clarify the specific problems of SQL syntax errors.

3. Common error handling patterns

In actual development, we usually combine mysqli::$errno and mysqli::$error for error handling to ensure that problems can be quickly discovered and fixed. Here are some common ways to deal with errors:

3.1 Catching SQL Errors

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

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

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if (!$result) {
    switch ($mysqli->errno) {
        case 1146:
            echo "Table not found: " . $mysqli->error;
            break;
        case 1064:
            echo "SQL syntax error: " . $mysqli->error;
            break;
        default:
            echo "Database error: " . $mysqli->error;
    }
} else {
    // Process query results
}
?>

In this example, we output specific error information based on different error codes (such as the table does not exist or SQL syntax errors), so that developers can know more clearly what is going on.

3.2 Use logging errors

In production environments, it is usually not recommended to directly output error information to users, especially database-related errors. To improve security, we can log error information to the log file for subsequent viewing.

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

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

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if (!$result) {
    // Logging
    error_log("SQL Error [" . $mysqli->errno . "]: " . $mysqli->error, 3, "/path/to/error.log");
    echo "An error occurred. Please check the log file for details.";
} else {
    // Process query results
}
?>

Here, the error_log function writes error information to the log file to help developers track and resolve issues.

4. Summary

Through the mysqli::$errno property, we can accurately capture errors in database operations and further locate and solve problems based on error codes and error information. Combined with a good error handling mechanism, developers can more effectively debug and optimize PHP and MySQL interactions.

Remember, good error handling not only helps developers debug code, but also enhances application stability and user experience.