How to print the structure information of mysqli_result

How to use the mysqli_result function to print the structure information of database query results?

In PHP programming, using MySQL databases is a common operation. To get data from a database, the mysqli extension is usually used. When conducting database queries, we sometimes need to view the structure information of the query results to understand the fields, types, etc. of the data.

The mysqli_result function can help us get this information. It is used in conjunction with the mysqli_query() function, returning an object containing the query results. Through this object, we can use some methods to view and manipulate the query results of the database.

1. How to use mysqli_result to print the structure information of the query result?

To print the structure information of the database query results, there are two main functions that can be used:

• mysqli_fetch_fields() : Gets the metadata of all fields in the query result.

• mysqli_fetch_field() : Gets the metadata of a field in the query result.

Sample code:

 <?php
// Configure database connections
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check if the connection is successful
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Execute a query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

// Check whether the query is successful
if ($result) {
    // Print the structure information of the query result
    echo "Structural information of query results:<br>";
    $fields = $result->fetch_fields();
    
    // Output field information
    foreach ($fields as $field) {
        echo "Field name: " . $field->name . "<br>";
        echo "Field Type: " . $field->type . "<br>";
        echo "Maximum length: " . $field->length . "<br>";
        echo "Whether it is allowed NULL: " . ($field->nullable ? 'yes' : 'no') . "<br><br>";
    }
} else {
    echo "Query failed: " . $conn->error;
}

// Close the connection
$conn->close();
?>

Code description:

1. Database connection : First, we create a connection to the MySQL database through new mysqli() .

2. Execute query : execute SQL query through $conn->query($sql) and return query results.

3. Get field information : $result->fetch_fields() returns an array containing all field metadata. Each field metadata is an object, and the detailed information of the field can be obtained through properties such as $field->name , $field->type, etc.

4. Print information : Use echo to output information such as the name, type, maximum length, and whether to allow empty.

2. Query the metadata of the result field

In the above code, each field object returned by the fetch_fields() method contains the following commonly used properties:

• name : The name of the field.

• table : The table name to which the field belongs.

• max_length : The maximum length of the field.

• type : The data type of the field (for example, integer, string, date, etc.).

• length : The length of the field.

• nullable : Whether the field can be NULL.

• primary_key : Whether the field is the primary key.

Using these properties can help developers gain insight into the table structure in the database and facilitate debugging and optimization during development.

3. Sample output:

Suppose the query user table has the following structure:

The output may be similar to:

 Structural information of query results:
Field name: id
Field Type: 3
Maximum length: 11
Whether it is allowed NULL: no

Field name: name
Field Type: 253
Maximum length: 50
Whether it is allowed NULL: no

Field name: email
Field Type: 253
Maximum length: 100
Whether it is allowed NULL: no

4. Other methods of mysqli_result function

In addition to the fetch_fields() method, the mysqli_result object also provides some other common methods to help us further process query results:

• num_fields() : Returns the number of fields in the query result.

• fetch_assoc() : Returns the query result in a row in the way of associating arrays.

• fetch_row() : Returns the query result in a row in the form of a numeric index.

• fetch_object() : Returns a row of query results in an object.

These methods can help you process query results more flexibly according to your needs.

5. Conclusion

Through the mysqli_result function and related methods, we can easily obtain and print the structural information of database query results. This is very helpful for development and debugging, analyzing database table structure and other tasks. When using mysqli extension, mastering how to obtain the structural information of query results will enable you to process data more efficiently.