English
English
In PHP's MySQLi extension, the mysqli_stmt object provides many methods for preparing and executing preprocessing statements. Among them, the mysqli_stmt::attr_get function can be used to obtain the attribute value of the current statement handle. By dynamically reading these attributes, we can more flexibly control data extraction and operation behavior, thereby meeting the database needs in different scenarios.
This article will explain in detail the usage of the mysqli_stmt::attr_get function. Based on actual examples, it demonstrates how to use this function to dynamically adjust the data extraction method to achieve more efficient and flexible database interaction.
mysqli_stmt::attr_get is one of the methods for preprocessing statement objects in MySQLi. Its main function is to return the specified attribute value of the statement handle. The function prototype is as follows:
public mysqli_stmt::attr_get(int $attr): mixed
Parameter $attr : the constant value of the attribute, which defines the attribute type to be obtained, such as the result set type, etc.
Return value: The current value of the corresponding attribute, the specific type depends on the attribute.
This method is often used with attr_set . By setting and obtaining attributes, we can dynamically control the execution behavior of MySQLi statements.
MYSQLI_STMT_ATTR_CURSOR_TYPE : Specifies the cursor type, which is used to determine how the result set is retrieved (for example, whether to use server-side cursors).
MYSQLI_STMT_ATTR_PREFETCH_ROWS : Controls the number of prefetched result rows, affecting performance and memory usage.
MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH : When enabled, get the maximum length of the string field.
MYSQLI_STMT_ATTR_EXECUTE_TIMEOUT : Timeout time for execution of the statement.
By dynamically reading and adjusting these properties, we can choose the appropriate extraction method according to business needs.
Suppose we have a need to query a large amount of data, and we want to control whether all prefetch data or load it on demand by setting the cursor type, thereby controlling memory usage.
<?php
$mysqli = new mysqli("m66.net", "username", "password", "database");
if ($mysqli->connect_errno) {
die("Connection failed: " . $mysqli->connect_error);
}
// Preparation statement
$stmt = $mysqli->prepare("SELECT id, name FROM users WHERE status = ?");
$status = 1;
$stmt->bind_param("i", $status);
// Set cursor type to server-side cursor，Avoid loading all results at once
$stmt->attr_set(MYSQLI_STMT_ATTR_CURSOR_TYPE, MYSQLI_CURSOR_TYPE_READ_ONLY);
// Execution statement
$stmt->execute();
// Read the current cursor type，Confirm the setup is successful
$currentCursorType = $stmt->attr_get(MYSQLI_STMT_ATTR_CURSOR_TYPE);
echo "The current cursor type is： " . $currentCursorType . PHP_EOL;
// Binding result variables
$stmt->bind_result($id, $name);
// Get results by row，Save memory
while ($stmt->fetch()) {
echo "userID: $id, name: $name" . PHP_EOL;
}
$stmt->close();
$mysqli->close();
?>
In this example, we set the cursor type to a read-only server-side cursor ( MYSQLI_CURSOR_TYPE_READ_ONLY ) through attr_set , so that the results will not be loaded into memory at once, but will be extracted line by line as needed. Then use attr_get to get the current cursor type to verify that the setting is successful.
This dynamic adjustment method is especially suitable for handling large data queries, which can effectively reduce memory pressure and improve application stability.
In actual projects, we can first call attr_get to get the current settings and select different extraction strategies according to different environments or parameters, for example:
// Get the current number of prefetched rows
$prefetchRows = $stmt->attr_get(MYSQLI_STMT_ATTR_PREFETCH_ROWS);
if ($prefetchRows > 100) {
// If the number of prefetched rows is large，Adopt server-side cursor
$stmt->attr_set(MYSQLI_STMT_ATTR_CURSOR_TYPE, MYSQLI_CURSOR_TYPE_READ_ONLY);
} else {
// otherwise，All prefetched by default
$stmt->attr_set(MYSQLI_STMT_ATTR_CURSOR_TYPE, MYSQLI_CURSOR_TYPE_NO_CURSOR);
}
In this way, the program can dynamically switch data extraction methods based on the current database status or operating environment to achieve more intelligent and flexible database operations.
mysqli_stmt::attr_get allows us to obtain the attribute value of the preprocessed statement, and can flexibly adjust the execution behavior of MySQLi with attr_set .
By dynamically controlling attributes such as cursor type and prefetching row count, more efficient result set processing can be achieved and adapted to different business scenarios.
Combined with the reading results of attr_get , adaptive database operation logic can be written to improve the robustness and performance of the system.
