English
English
When developing PHP-based database applications, it’s often necessary to understand the structure of fields in the query results, such as field names, data types, maximum lengths, and more. This information is very useful for dynamically generating tables, validating data formats, and even automatically creating form elements. Fortunately, PHP offers a powerful function — mysqli_result::fetch_field — that helps us easily retrieve this field metadata.
This article will provide a comprehensive overview of how to use the mysqli_result::fetch_field function to inspect the field structure of query results, along with concrete code examples to deepen your understanding.
mysqli_result::fetch_field() is a method of the mysqli_result class that returns an object representing the information of a single field in the result set. Each call to this method returns the next field's information until no more fields are left.
The returned object is an stdClass object containing multiple properties, including:
name: the field name
orgname: the original field name (if an alias was used)
table: the name of the table the field belongs to
orgtable: the original table name
def: the default value (deprecated, always null)
db: the database name
catalog: the database catalog (usually "def")
max_length: the maximum length of this field in the result set
length: the defined length of the field
charsetnr: the character set number
flags: field flags (such as whether it is a primary key or can be NULL)
type: the field type (e.g., MYSQLI_TYPE_STRING)
decimals: number of decimal places
Here is a basic example showing how to connect to a database and retrieve the field structure information from a query result.
<?php
// Database connection parameters
$host = "localhost";
$user = "root";
$password = "your_password";
$database = "test_db";
// Establish database connection
$conn = new mysqli($host, $user, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute query
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "Field structure information:
";
while ($field = $result->fetch_field()) {
echo "Field name: " . $field->name . "<br>";
echo "Table: " . $field->table . "<br>";
echo "Original field name: " . $field->orgname . "<br>";
echo "Type: " . $field->type . "<br>";
echo "Length: " . $field->length . "<br>";
echo "Nullable: " . ($field->flags & MYSQLI_NOT_NULL_FLAG ? "No" : "Yes") . "<br>";
echo "--------------------------<br>";
}
} else {
echo "No results.";
}
// Close connection
$conn->close();
?>
type returns an integer constant representing the field type, for example:
MYSQLI_TYPE_LONG (integer)
MYSQLI_TYPE_VAR_STRING (variable-length string)
MYSQLI_TYPE_DATETIME (date and time)
You can define a mapping array to convert these constants into more readable text.
Similarly, flags is a bitmask indicating various field attributes, such as whether it is a primary key or auto-increment. You can use bitwise operations to check if a particular flag is set.
Dynamically generate table headers: You can use fetch_field() to dynamically get field names as HTML table headers, which is especially useful when the number of columns is unknown.
Field validation: When handling user input or importing data, pre-fetching field structure helps verify if field types and lengths meet expectations.
Automated development: Combine field metadata to automatically generate forms and data validation rules.
