English
English
When using PHP's mysqli extension for database operations, we may get the actual length of each field in the current row through the mysqli_result::$lengths property. This property is especially useful when dealing with binary or variable length fields (such as BLOB, TEXT). But how do you detect if you are worried about whether the field length exceeds the expected or defined limit? This article will take you step by step to understand how to achieve this goal.
mysqli_result::$lengths is an attribute of the mysqli_result object. It returns an array where each element in the array represents the byte length of each field in a row in the current result set. This value is the actual length of the data, not the defined length of the database field.
Suppose we have the following MySQL table structure:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(20),
bio TEXT
);
We want to make sure that the username field fetched from the database does not exceed the limit of VARCHAR(20) , i.e. 20 characters.
We can combine field definition and $result->lengths to realize the function of detecting whether the field is too long.
<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
$query = "SELECT id, username, bio FROM users";
$result = $mysqli->query($query);
if ($result) {
// Get field definition information
$fields = $result->fetch_fields();
while ($row = $result->fetch_assoc()) {
$lengths = $result->lengths;
foreach ($fields as $index => $field) {
$fieldName = $field->name;
$maxLength = $field->length;
// Notice：for VARCHAR and CHAR Fields，$field->length It's the character length * Maximum number of bytes in character set
// Actual conversion needs to be combined with character set，Assume here UTF-8（most 3-4 byte/character）
$expectedBytes = $maxLength * 4;
if ($lengths[$index] > $expectedBytes) {
echo "Fields '{$fieldName}' The content length exceeds the expected limit：{$lengths[$index]} byte\n";
}
}
}
}
$mysqli->close();
?>
$field->length is usually given by the maximum number of bytes and cannot be directly equivalent to the character length. Especially when using multi-byte character sets (such as UTF-8), the relationship between bytes and characters must be converted.
For BLOB or TEXT type fields, MySQL itself allows larger lengths, so you need to customize the limit criteria based on business logic.
If the field content has been escaped or encoded, the byte length may also be increased. Therefore, $result->lengths can only be used as an estimation method. It is still recommended to combine judgment with business rules when actually used.
Fields that exceed the limit can be logged into the log for troubleshooting data source problems or business logic errors.
if ($lengths[$index] > $expectedBytes) {
file_put_contents("/var/log/field_overflow.log", date('c') . " - Fields '{$fieldName}' Limit exceeded。length: {$lengths[$index]}\n", FILE_APPEND);
}
You can also alarm through the web interface, for example, call an interface similar to https://gitbox.net/api/notify?type=length_warning to alert.
Through the combination of mysqli_result::$lengths and fetch_fields() , we can effectively monitor whether the field length in the database meets expectations. In multilingual and multi-character set systems, this detection is particularly critical and can help developers discover potential data problems in advance. It is recommended to integrate such logic in high-risk processes such as data import and user input to improve the robustness of the system.
