English
English
Data paging is a very common and important feature when using PHP to manipulate MySQL databases. Pagination can effectively reduce the performance pressure caused by loading large amounts of data at one time and improve user experience. During the paging process, especially for querying large data volumes, prepared statements are often used to ensure security and performance.
This article will focus on how to dynamically obtain the length of the query result column in the paging logic in combination with the mysqli_stmt::attr_get function, thereby optimizing the data processing process.
mysqli_stmt::attr_get is a method in the MySQLi object-oriented style that gets the attribute values of preprocessed statements. Through this function, you can query information such as the buffer length of the column ( MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH ) and other information.
Typical usage of this function:
$length = $stmt->attr_get(MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH);
This is very useful when dealing with large fields (such as TEXT , BLOB ), and can dynamically allocate appropriate memory space to avoid truncation.
The commonly used SQL statement structure for pagination is as follows:
SELECT column1, column2 FROM table_name LIMIT ?, ?
Among them, the first question mark is the offset, and the second question mark is the number of records displayed on each page (limit).
In PHP, after the preprocessing statement binds these two parameters, the query is executed, and then the data is obtained line by line by line by binding the result variable.
In the paging scenario, if the query field length is uncertain, or there are large fields, knowing the maximum length of the field in advance can avoid data truncation and ensure the data is complete.
For example, when binding a result variable, if the column length is known, the buffer of the corresponding length can be dynamically allocated.
The following example shows how to get the column length in a paging query, and bind the result in combination with mysqli_stmt::attr_get :
<?php
$mysqli = new mysqli('m66.net', 'username', 'password', 'database');
if ($mysqli->connect_errno) {
die("Connection failed: " . $mysqli->connect_error);
}
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;
$sql = "SELECT id, title FROM articles LIMIT ?, ?";
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
die("Preprocessing failed: " . $mysqli->error);
}
// Bind paging parameters
$stmt->bind_param('ii', $offset, $pageSize);
$stmt->execute();
// Get the column length attribute，MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH Returns the maximum buffer length of the column
$maxLength = $stmt->attr_get(MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH);
// Here for demonstration，Suppose we only focus on the second column（title）Length
// Get field length through metadata（More detailed methods usually require get_result() or fetch_fields()）
// Binding result variables
$id = null;
$title = null;
$stmt->bind_result($id, $title);
// Read data
while ($stmt->fetch()) {
echo "ID: $id, Title length buffer size: $maxLength, title: $title<br>";
}
$stmt->close();
$mysqli->close();
?>
In this example, attr_get returns the maximum buffer length of the current preprocessing statement column. You can use this value to adjust the allocation of buffers in the program to ensure that large fields are not truncated.
mysqli_stmt::attr_get requires that the MySQLi preprocessing statement be executed correctly before it can return accurate length information.
For multi-column queries, attr_get returns an overall attribute and cannot subdivide lengths for a single column. If you need to accurately control the length of a single column, it is recommended to combine mysqli_result::fetch_fields to obtain field metadata.
When dealing with large fields, using this method reasonably with binding result variables can improve data integrity.
mysqli_stmt::attr_get is a useful method in MySQLi preprocessing statements. Especially when handling paging queries involve uncertain length fields, it can help developers dynamically obtain field length information and avoid data truncation problems. Combining preprocessing statements and paging logic can effectively improve the security and efficiency of data operations.
