Best practices for using mysqli::$info with mysqli_error()

1. Introduction to mysqli_error()

mysqli_error() is one of the most familiar error handling functions for developers. Its function is to return a string describing the error when the database operation fails. For example, when executing an incorrect SQL statement:

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

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

if (!$result) {
    echo "SQL Execution failed: " . mysqli_error($mysqli);
}

The output will be an error message like this:

 SQL Execution failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC * FROM users' at line 1

This is helpful for positioning syntax errors.

2. mysqli::$info provides more context

Unlike mysqli_error() , mysqli::$info provides more context information about the statement processing results after the SQL statement is executed successfully. Especially when executing statements such as INSERT , UPDATE , DELETE, etc., it can tell you how many rows are affected, whether duplicate rows are skipped, etc.

For example:

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

$sql = "UPDATE users SET status='active' WHERE last_login < '2024-01-01'";
$mysqli->query($sql);

echo "Impact details: " . $mysqli->info;

Possible outputs are similar:

 Impact details: Rows matched: 3  Changed: 2  Warnings: 0

This means that a total of 3 rows matched, but only 2 rows were actually modified.

3. Best practices for the use of both

When executing SQL statements, these two functions can be combined: if the statement fails, use mysqli_error() to obtain the error information; if the execution is successful, use mysqli::$info to obtain the execution details. This method is suitable for debugging, logging, and even displaying operation results in the background management panel.

A typical encapsulation function is as follows:

 function executeQuery($mysqli, $sql) {
    if ($mysqli->query($sql) === TRUE) {
        return [
            'success' => true,
            'info' => $mysqli->info
        ];
    } else {
        return [
            'success' => false,
            'error' => mysqli_error($mysqli)
        ];
    }
}

Call example:

 $response = executeQuery($mysqli, "UPDATE users SET status='inactive' WHERE id IN (1,2,3)");

if ($response['success']) {
    echo "Update successfully，Details are as follows：" . $response['info'];
} else {
    echo "Update failed，error message：" . $response['error'];
}

Output (on success):

 Update successfully，Details are as follows：Rows matched: 3  Changed: 3  Warnings: 0

Output (on failure):

 Update failed，error message：Unknown column 'statuz' in 'field list'

4. Examples of practical application scenarios

1. Data migration and synchronization tasks <br> When writing a large amount of data, knowing whether the data is updated or skipped is very critical to judging whether the task is successful or not.

2. Backstage log system <br> For administrators, it is not only necessary to know whether an operation succeeds or fails, but also to understand the specific number of records that affect it.

3. Automated testing <br> When certain rows are expected to affect, using these two functions in combination can verify the correctness of SQL statements.