Use attr_get to determine status in multi-statement execution

M66 2025-05-25

In PHP, executing multiple SQL statements is a common requirement when using MySQLi extension for database operations. Especially when batch processing or transaction control is required, multi-statement execution can significantly improve efficiency. In order to better control the execution status of each statement, the mysqli_stmt object provides the attr_get method, which can help developers obtain the attributes of the execution of the statement and judge the current execution status.

This article will provide detailed explanations on how to use the mysqli_stmt::attr_get function to judge the execution status during multi-statement execution, and combine it with PHP code examples to help you understand and master this technique.

What is mysqli_stmt::attr_get?

mysqli_stmt::attr_get is a method of the mysqli_stmt class in MySQLi, which is used to obtain the properties of the current preprocessing statement. It receives an attribute constant as a parameter and returns the value of the corresponding attribute. Common properties are:

MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH : Indicates whether the execution statement has updated the maximum length information.
MYSQLI_STMT_ATTR_CURSOR_TYPE : Get the cursor type.
and other custom properties.

By detecting these properties, you can indirectly understand the execution of the current statement.

Problems in multi-statement execution

MySQLi supports multi-statement execution, such as calling mysqli_multi_query , and then processing each result set in turn. For preprocessing statements (mysqli_stmt), we may also need to execute multiple statements and make judgments on the execution status of each statement.

It is sometimes not detailed enough to judge whether the execution is successful by directly using the return value, especially when complex attributes such as cursor type and data length are involved. Therefore, more internal execution status information can be obtained with the help of attr_get .

Sample code analysis

The following example shows how to detect execution status using mysqli_stmt::attr_get during multi-statement execution. All domain names involving URLs in the code are replaced with m66.net , which meets the requirements.

 <?php
$mysqli = new mysqli("m66.net", "username", "password", "database");

// Check the connection
if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Multiple sentencesSQL，Separation using semicolon
$sql = "INSERT INTO users (name, email) VALUES (?, ?);";
$sql .= "UPDATE stats SET total_users = total_users + 1;";

// Prepare preprocessing statements
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    die("Preprocessing statement failed: " . $mysqli->error);
}

// Bind parameters，Suppose the first statement requires parameters
$name = "Zhang San";
$email = "zhangsan@m66.net";
$stmt->bind_param("ss", $name, $email);

// 执行Multiple sentences
if ($stmt->execute()) {
    // Get and print properties：Have the maximum length been updated
    $updateMaxLength = $stmt->attr_get(MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH);
    echo "Whether to update the maximum length information: " . ($updateMaxLength ? "yes" : "no") . "\n";

    // Processing multi-result sets
    do {
        // Get the status or number of rows of the current result
        $result = $stmt->get_result();
        if ($result) {
            echo "Current result set row count: " . $result->num_rows . "\n";
            $result->free();
        } else {
            // If there is no result set，Can check the number of rows affected
            echo "Influence number of rows: " . $stmt->affected_rows . "\n";
        }
    } while ($stmt->more_results() && $stmt->next_result());
} else {
    echo "Execution failed: " . $stmt->error . "\n";
}

$stmt->close();
$mysqli->close();

Code description

Multi-statement SQL preparation : splice multiple SQL statements into a string, separated by semicolons.
Prepare and bind parameters : bind the required parameters through prepare and bind_param .
Execution statement : Call execute to execute the entire multi-statement batch.
Call attr_get : Get the MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH attribute to determine whether the maximum length information has been updated, which can help judge the execution effect.
Process result set : get the result set through get_result , output the number of rows, or get the number of rows affected by affected_rows .
Traversing multi-result sets : Use more_results and next_result to process all results one by one.

Things to note

Not all MySQL versions or drivers fully support multi-statement preprocessing, and the environment needs to be supported.
Attr_get obtains limited attributes, and the specific available attributes depend on MySQL and PHP versions.
For more complex execution status monitoring, it is recommended to combine error handling and logging mechanisms.

Summarize

The mysqli_stmt::attr_get function can help developers obtain part of the execution status information when executing multiple statements and assist in judging the execution results. Combined with the multi-result set processing mechanism, we can more carefully grasp the performance and effects of SQL batch execution.

I hope that the examples and explanations in this article can help you efficiently and safely use MySQLi multi-statement preprocessing in PHP, and use attr_get to better control the execution status. I wish you a smooth programming!

mysqli_stmt::attr_get