How should mysqli::get_warnings and mysqli::query be used together? Best Practice Sharing

gitbox 2025-06-04

When using PHP's mysqli extension for database operations, we usually pay attention to whether the query is successful and whether an error has been thrown, but there is a situation that is often ignored:. If these warnings are not specifically dealt with, they may hide potential problems such as field truncation, default values being replaced, etc. This article will share how to use mysqli::query() and mysqli::get_warnings() in combination to capture these warnings and give best practices.

Why do you need to follow SQL Warnings?

Consider a SQL like this:

 $sql = "INSERT INTO users (username, email) VALUES ('this_is_a_very_long_username_that_will_be_truncated', 'user@example.com')";

Assuming that the maximum length of the username field is 30 characters, the actual execution of the above SQL will be truncated. Although no error will be thrown, a warning will be generated. If we ignore it, the data is quietly "processed" without you knowing it.

What does mysqli::get_warnings do?

mysqli::get_warnings() is a method used to obtain SQL warning information from the previous operation. It returns an object containing warning levels, code and messages that can be used to debug or log.

Actual use examples

Let's first look at a minimal example to understand the basic usage:

 <?php

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

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

$sql = "INSERT INTO demo (name) VALUES ('Extra-long string triggers warning Extra-long string triggers warning Extra-long string triggers warning')";

if ($mysqli->query($sql)) {
    echo "Insert successfully\n";

    // Check for warnings
    $warning = $mysqli->get_warnings();
    while ($warning) {
        echo "warn: [{$warning->errno}] {$warning->message}\n";
        $warning = $warning->next();
    }

} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();

Practical suggestions: log warnings

In actual projects, it is not recommended to output warnings directly to the user, but to log them in the log. For example:

 function log_warnings($mysqli) {
    $warning = $mysqli->get_warnings();
    while ($warning) {
        error_log("SQL Warning [{$warning->errno}]: {$warning->message}");
        $warning = $warning->next();
    }
}

$sql = "UPDATE users SET username='Excessively long names will be cut off' WHERE id=1";

if ($mysqli->query($sql)) {
    log_warnings($mysqli);
} else {
    error_log("SQL Error: " . $mysqli->error);
}

Logging into the log not only helps to troubleshoot problems later, but also provides feedback on SQL quality in the test environment.

Things to note

get_warnings() only works for the last query;
It returns an object similar to a linked list, traversed by next() ;
Not all drivers fully support warnings, and the database configuration needs to be tested before use;
mysqli_report() cannot replace get_warnings() , the former pays more attention to errors;
Use prepare statements ( prepare ) to trigger and get warnings as they do when binding parameters.

Supplement: Used in production environments

You may want to summarize warnings in production and report them regularly, which can be done by encapsulating a database assistant class:

 class DB {
    protected $mysqli;

    public function __construct() {
        $this->mysqli = new mysqli("localhost", "user", "password", "appdb");
    }

    public function query($sql) {
        $result = $this->mysqli->query($sql);
        if ($result) {
            $this->logWarnings();
        } else {
            error_log("SQL Error: " . $this->mysqli->error);
        }
        return $result;
    }

    protected function logWarnings() {
        $warning = $this->mysqli->get_warnings();
        while ($warning) {
            error_log("[DBwarn] {$warning->errno} - {$warning->message}");
            $warning = $warning->next();
        }
    }
}

$db = new DB();
$db->query("UPDATE posts SET title='标题过长会触发warn，Pay attention to length control' WHERE id=42");

Summarize

mysqli::query() is a powerful tool for executing SQL, while mysqli::get_warnings() is an important supplement to ensure data integrity. Don't ignore these warning messages, they often hide your future bugs. Developing the habit of capturing and handling warnings in a development or testing environment will significantly improve the robustness and maintainability of your project.

Remember: If the error causes the program to lapse, the warning will cause you to lose the trap.

Related Tags:
mysqli