How to use mysqli::get_warnings and mysqli::error_list to do stronger log analysis

1. Understand mysqli::get_warnings and mysqli::error_list

1.1 mysqli::get_warnings()

This method returns a mysqli_warning object, allowing traversal of all warnings returned by the database. Applicable to SQL that executes successfully but returns warning.

 $mysqli = new mysqli("localhost", "user", "pass", "database");
$mysqli->query("YOUR SQL HERE");

if ($warning = $mysqli->get_warnings()) {
    do {
        echo "Warning: " . $warning->message . PHP_EOL;
    } while ($warning->next());
}

1.2 mysqli::error_list

In contrast, error_list is an array containing all error messages (including error code and message) that is suitable for capturing failed SQL execution information.

 $result = $mysqli->query("BROKEN SQL");
if (!$result) {
    foreach ($mysqli->error_list as $error) {
        echo "Error [{$error['errno']}]: {$error['error']}" . PHP_EOL;
    }
}

2. Integrate logging logic

In order to achieve more complete logging, we can encapsulate a log function, record errors and warning information, and record relevant contexts (such as SQL statements, execution time, access source, etc.) for subsequent troubleshooting and optimization.

2.1 Core code structure

 function log_db_activity(mysqli $mysqli, string $sql, $result): void {
    $log = [];

    // RecordSQLStatement
    $log['sql'] = $sql;
    $log['time'] = date('c');

    // Check for errors
    if (!$result && !empty($mysqli->error_list)) {
        $log['errors'] = $mysqli->error_list;
    }

    // Check warning
    if ($warning = $mysqli->get_warnings()) {
        $log['warnings'] = [];
        do {
            $log['warnings'][] = [
                'errno' => $warning->errno,
                'message' => $warning->message,
            ];
        } while ($warning->next());
    }

    if (!empty($log['errors']) || !empty($log['warnings'])) {
        // Can be replaced with a database or persistence method
        file_put_contents(__DIR__ . '/db_log.json', json_encode($log, JSON_PRETTY_PRINT) . PHP_EOL, FILE_APPEND);
    }
}

2.2 How to use

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

$sql = "INSERT INTO users (id, name) VALUES (1, 'Tom')";
$result = $mysqli->query($sql);

log_db_activity($mysqli, $sql, $result);

3. Value manifestation in typical scenarios

3.1 Index failure warning

MySQL will give warning when executing some queries that may cause index failure, such as:

 $sql = "SELECT * FROM users WHERE name LIKE '%abc%'";

Although this SQL is legal and can return results, get_warnings() can reveal its efficiency issues, which is conducive to optimizing indexes or adjusting query logic.

3.2 Data truncation warning

Inserting content that exceeds the length of the field may not fail, but a warning will be triggered. Through logs, field design or front-end verification logic can be discovered and optimized in a timely manner.

4. Integrate with remote log service

In large projects, logs should not be saved only in local text. You can further push these logs to a remote log collection service such as https://gitbox.net/api/logs :

 function send_log_to_remote(array $log): void {
    $ch = curl_init('https://gitbox.net/api/logs');
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode($log),
    ]);
    curl_exec($ch);
    curl_close($ch);
}