Why is $errno always 0, but the query is not successful?

MySQL extension is a common choice when using PHP and MySQL for database operations. For errors in debugging database queries, mysqli::$errno and mysqli::$error are very useful tools. They can help developers get database error codes and error information. However, sometimes developers may encounter this situation: mysqli::$errno returns 0, but the query operation still fails. So, why does this happen? Let’s discuss it in depth below.

1. The role of mysqli::$errno

The mysqli::$errno property contains the error code that occurred in the last MySQL operation. Typically, 0 means no error occurred, while a non-zero value indicates that some error occurred. This is the error code in the MySQL database system. Together with mysqli::$error , it can help us quickly locate the problem.

2. The case where mysqli::$errno returns 0

Despite the query failure, mysqli::$errno still returns 0 , which may be due to several reasons:

2.1 The SQL query itself does not generate a MySQL error

mysqli::$errno will only return the error code on the MySQL server. If your query does not trigger a MySQL error (such as syntax errors, connection problems, etc.), then mysqli::$errno may still be 0 . At this time, it should be noted that even if the query returns a blank result, it does not mean that an error occurred during the execution of MySQL.

For example, when the query does not match any results, MySQL does not return an error, but simply returns an empty result set. At this time , mysqli::$errno is still 0 , and the query itself does not fail.

2.2 Non-error type SQL query is used

Some SQL queries (such as INSERT , UPDATE , or DELETE ) may be executed successfully, but the number of rows affected is 0 , that is, the data in the database is not actually modified. While this is not a mistake, the developer may think that the query is not successful, resulting in misunderstanding. In order to better determine whether it is successful, you can check mysqli_affected_rows() to confirm whether any rows are affected.

2.3 Database connection issues

Sometimes, the database connection may have expired or been disconnected before querying, and mysqli::$errno does not immediately return an error. You can check mysqli::$connect_errno to confirm whether the database connection is normal.

3. How to debug this problem?

When you find that mysqli::$errno always returns 0 but the query still fails, you can take the following method to debug:

3.1 Use mysqli::$error to get more information

The mysqli::$error property contains MySQL error information related to the current operation. Even if mysqli::$errno returns 0 , you can still get some clues through mysqli::$error . Even if errno is 0 , error may give some additional hints.

 if ($mysqli->errno) {
    echo "Error number: " . $mysqli->errno . "<br>";
    echo "Error message: " . $mysqli->error . "<br>";
}

3.2 Check SQL statements

Make sure your SQL query has no syntax or logic errors. You can print out the query and execute it directly in a database management tool such as phpMyAdmin to see if the expected result is returned.

3.3 Check the number of rows affected

For INSERT , UPDATE , or DELETE queries, the results returned by the mysqli_affected_rows() function should be checked after execution. If 0 is returned, it means that although the query has been executed, no data has been modified.

 $affected_rows = $mysqli->affected_rows;
if ($affected_rows === 0) {
    echo "No rows were affected by the query.<br>";
}

3.4 Check database connections

If you suspect that it is a database connection problem, you can use mysqli_connect_errno() to check whether the database connection is successful.

 if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

4. Summary

When using mysqli::$errno , if it returns 0 , but the query still fails, it may be that the query does not generate a MySQL error, the query does not modify any data, or there is another type of problem rather than a MySQL error. To solve this problem, developers should combine tools such as mysqli::$error , mysqli_affected_rows() to perform comprehensive debugging. Through these steps, you can more effectively identify and resolve problems in database queries.

 // Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Query
$query = "SELECT * FROM some_table WHERE id = 1";
$result = $mysqli->query($query);

// Result processing
if ($mysqli->errno) {
    echo "Error number: " . $mysqli->errno . "<br>";
    echo "Error message: " . $mysqli->error . "<br>";
}

// Close the connection
$mysqli->close();