Is there a complete code example for mysqli_stmt::result_metadata to get the result set structure? How to write it?

In PHP, mysqli_stmt::result_metadata is a commonly used method to fetch the metadata (field structure) of the result set associated with a prepared statement. This method is useful for dynamically generating query result columns and performing further operations such as binding columns. Below, I will show a complete code example to demonstrate how to use mysqli_stmt::result_metadata to retrieve the result set structure.

1. Connecting to the Database

First, we need to connect to the database using mysqli. Assuming you already have a MySQL database, here's the basic connection code:

<?php
$host = 'localhost';
$username = 'root';
$password = 'password';
$dbname = 'test_database';
<p>// Create connection<br>
$conn = new mysqli($host, $username, $password, $dbname);</p>
<p>// Check connection<br>
if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}<br>
?><br>

2. Preparing the SQL Query

Let's assume we have a table called users with fields id, name, and email. We want to fetch the metadata for these fields. First, prepare the SQL query:

$sql = "SELECT id, name, email FROM users";
$stmt = $conn->prepare($sql);
<p>if ($stmt === false) {<br>
die('MySQL prepare failed: ' . $conn->error);<br>
}<br>

3. Fetching Result Set Metadata

We use the mysqli_stmt::result_metadata method to fetch the metadata of the result set. This method returns a mysqli_result object, which contains the field information of the query result. Next, we will loop through the field information and display the name of each field.

$result_metadata = $stmt->result_metadata();
<p>if ($result_metadata === false) {<br>
die('Unable to fetch result set metadata');<br>
}</p>
<p>$columns = [];<br>
while ($column = $result_metadata->fetch_field()) {<br>
$columns[] = $column->name;<br>
}</p>
<p>echo "Field names in the query result: <br>";<br>
foreach ($columns as $column_name) {<br>
echo $column_name . "<br>";<br>
}<br>

4. Binding Columns

Once the metadata is fetched, you can use the mysqli_stmt::bind_result method to bind each column from the result set to a PHP variable. Here's an example of how to bind columns and retrieve results:

// Bind columns
$stmt->bind_result($id, $name, $email);
<p>// Execute query<br>
$stmt->execute();</p>
<p>// Fetch results<br>
while ($stmt->fetch()) {<br>
echo "ID: $id, Name: $name, Email: $email <br>";<br>
}</p>
<p>// Close statement<br>
$stmt->close();<br>

5. Full Example

Here is the complete code example combining all the above steps:

<?php
$host = 'localhost';
$username = 'root';
$password = 'password';
$dbname = 'test_database';
<p>// Create connection<br>
$conn = new mysqli($host, $username, $password, $dbname);</p>
<p>// Check connection<br>
if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}</p>
<p>$sql = "SELECT id, name, email FROM users";<br>
$stmt = $conn->prepare($sql);</p>
<p>if ($stmt === false) {<br>
die('MySQL prepare failed: ' . $conn->error);<br>
}</p>
<p>// Fetch result set metadata<br>
$result_metadata = $stmt->result_metadata();</p>
<p>if ($result_metadata === false) {<br>
die('Unable to fetch result set metadata');<br>
}</p>
<p>$columns = [];<br>
while ($column = $result_metadata->fetch_field()) {<br>
$columns[] = $column->name;<br>
}</p>
<p>echo "Field names in the query result: <br>";<br>
foreach ($columns as $column_name) {<br>
echo $column_name . "<br>";<br>
}</p>
<p>// Bind columns<br>
$stmt->bind_result($id, $name, $email);</p>
<p>// Execute query<br>
$stmt->execute();</p>
<p>// Fetch results<br>
while ($stmt->fetch()) {<br>
echo "ID: $id, Name: $name, Email: $email <br>";<br>
}</p>
<p>// Close statement<br>
$stmt->close();</p>
<p>// Close connection<br>
$conn->close();<br>
?><br>

6. Conclusion

By using mysqli_stmt::result_metadata, you can easily retrieve the result set structure of a prepared statement query. Once you have the field information, you can dynamically bind the columns and extract query results as needed. This is very useful when handling complex queries and generating dynamic pages.