English
English
In PHP, when we perform database operations using the extension, sometimes it’s not enough to get the query results themselves; we also need to know the detailed information about the fields returned by the query, such as field names, types, lengths, and so on. This information is referred to as field metadata.
This article will explain how to use mysqli::stmt_init in combination with the prepare() and result_metadata() methods to retrieve database field metadata.
First, we need to connect to the database using the mysqli class.
<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
<p>if ($mysqli->connect_errno) {<br>
die('Connection failed: ' . $mysqli->connect_error);<br>
}<br>
?><br>
Next, we initialize a mysqli_stmt object using stmt_init() and prepare an SQL query using the prepare() method.
<?php
$stmt = $mysqli->stmt_init();
<p>if (!$stmt->prepare('SELECT id, name, email FROM users WHERE status = ?')) {<br>
die('Preparation failed: ' . $stmt->error);<br>
}<br>
?><br>
Assume we want to query users with status = 'active':
<?php
$status = 'active';
$stmt->bind_param('s', $status);
<p>if (!$stmt->execute()) {<br>
die('Execution failed: ' . $stmt->error);<br>
}<br>
?><br>
Now, the key step — we call the result_metadata() method to retrieve the field metadata of the query result.
<?php
$metadata = $stmt->result_metadata();
<p>if ($metadata) {<br>
while ($field = $metadata->fetch_field()) {<br>
echo "Field Name: " . $field->name . "<br>";<br>
echo "Field Type: " . $field->type . "<br>";<br>
echo "Maximum Length: " . $field->max_length . "<br>";<br>
echo "<hr>";<br>
}<br>
$metadata->free();<br>
} else {<br>
echo "No metadata available.";<br>
}<br>
?><br>
Here, fetch_field() returns an object containing detailed information about the field, with common properties including:
name → Field name
type → Field type (numeric code, refer to the manual for mapping)
max_length → Maximum length of the field (Note: this applies only to the result set)
Finally, don’t forget to release the statement and close the database connection:
<?php
$stmt->close();
$mysqli->close();
?>
Assuming the query returned three fields: id, name, email, the output might look like this:
Field Name: id
Field Type: 3
Maximum Length: 0
-----------------------
Field Name: name
Field Type: 253
Maximum Length: 0
-----------------------
Field Name: email
Field Type: 253
Maximum Length: 0
Note that the numeric values for Field Type need to be referenced in the MySQL manual for type constants, such as 3 for MYSQLI_TYPE_LONG and 253 for MYSQLI_TYPE_VAR_STRING.
