Printing mysqli_result for Metadata Queries
When you execute a query like DESCRIBE or SHOW CREATE TABLE using mysqli_query(), the function returns an instance of mysqli_result. This object behaves differently from the result set returned by a standard SELECT statement. While a SELECT query typically yields rows that you iterate over with a loop, metadata queries return a single row containing multiple columns describing the table structure or definition.
To display the output of DESCRIBE students in a format resembling the MySQL command-line client (the ASCII grid shown in your request), you must first fetch the result as an associative array and then manually format the strings. There is no built-in method to "pretty print" the raw object directly to the browser.
Here is how to handle the DESCRIBE query:
$link = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DATABASE);
// Execute the DESCRIBE query
$result = mysqli_query($link, "DESCRIBE students");
if ($result) {
// Fetch the single row as an associative array
$row = mysqli_fetch_assoc($result);
// Define column names to match the desired output layout
$headers = ['Field', 'Type', 'Null', 'Key', 'Default', 'Extra'];
echo "<table border='1' cellpadding='5'>";
// Print the header row
echo "<tr>";
foreach ($headers as $header) {
echo "<th>$header</th>";
}
echo "</tr>";
// Iterate through each column of the result row
foreach ($row as $key => $value) {
echo "<tr>";
echo "<td>" . htmlspecialchars($key) . "</td>";
echo "<td>" . htmlspecialchars($value) . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "Query failed: " . mysqli_error($link);
}
The core logic here relies on mysqli_fetch_assoc(). This function extracts the data from the first (and only) row returned by the metadata query. By iterating over this array, you can map the internal field names (Field, Type, etc.) to their corresponding values. The resulting HTML table mimics the visual structure of the terminal output without requiring external libraries.
For the second part of your request, printing the result of SHOW CREATE TABLE students follows a similar pattern but requires handling slightly different column names. The SHOW CREATE TABLE command returns columns such as Create Table, Create Options, and sometimes View Algorithm or View Definition depending on context, though for standard tables it is primarily Create Table and Create Options.
$result = mysqli_query($link, "SHOW CREATE TABLE students");
if ($result) {
$row = mysqli_fetch_assoc($result);
// The primary column containing the SQL definition
$tableDefinition = $row['Create Table'];
echo "<pre>";
echo htmlspecialchars($tableDefinition);
echo "</pre>";
} else {
echo "Query failed: " . mysqli_error($link);
}
In this scenario, mysqli_fetch_assoc() returns an array where the keys are dynamic based on the specific command output. Accessing $row['Create Table'] retrieves the full SQL statement used to create the table. Wrapping it in htmlspecialchars() is crucial when echoing into HTML contexts to prevent breaking out of tags or introducing XSS vulnerabilities, although the content itself usually contains only SQL keywords.
Handling Errors and Null Values
A common pitfall when working with metadata queries is not checking if the result set is actually valid before fetching rows. While DESCRIBE and SHOW commands rarely fail on valid tables, they can return false if the table does not exist or if there are permission issues. Always verify that $result is truthy before calling mysqli_fetch_assoc().
Additionally, be mindful of how NULL values are represented. In some MySQL configurations or specific client tools, a NULL value in a column might appear as an empty string or "NULL" depending on the client driver settings. When fetching via PHP, you will typically get the literal string 'NULL' if that is what the database returns for that specific cell, rather than an actual SQL NULL type which would be represented as null in the PHP array. If your application logic expects to distinguish between a NULL value and an empty string, check explicitly using isset($row['Field']) or similar checks before processing the data.
For developers building robust applications, consider whether you really need to replicate the command-line output exactly. If the goal is debugging, echoing the raw SQL definition from SHOW CREATE TABLE is often sufficient. However, if you are building an admin panel where users expect a specific visual layout of column definitions, constructing an HTML table with fixed headers (as shown in the first example) provides a consistent user experience regardless of minor variations in how MySQL formats the internal result set.