How to Describe Database Table With Mysqli_query in PHP
This article will teach you how to use mysqli_query to describe a database table. We’ll do the description using the DESCRIBE command in SQL.
At the same time, we’ll print the result, and it’ll look like what you’ll see on the MySQL console.
Set Up the Test Database
The test database that we’ll set up will contain a single table. So, if you have a MySQL database that you can use, skip this section.
To get started with the database, download and install XAMPP from Apache Friends. Once you install XAMPP, you’ll have access to MySQL through a console or phpMyAdmin.
Meanwhile, for this article, we’ll access MySQL from the console. So, do the following to access the MySQL console in XAMPP.
-
Launch the XAMPP control panel.
-
Click on
Shellon the right side of the dialog window. This will launch the console. -
Type
mysql -u root -pand press the Enter key on your keyboard.
This command assumes the default database user and password. The default user is root, and the password is empty.
Once logged into MySQL, create a database with the following query.
CREATE database user_details;
Switch to the new database using the following.
USE user_details;
Now, create a table in the database using the following query.
CREATE TABLE bio_data (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
PRIMARY KEY (id)) ENGINE = InnoDB;
Describe the Table With mysqli_query in PHP
To describe the table using mysqli_query, we’ll write PHP code that will do the following.
- Connect to the database.
- Use the
DESCRIBEcommand on thebio_datatable using the OOP version ofmysqli_query. - Fetch the result using
fetch_all. - Get the keys for the first row. The first row will be the table header.
- Print the table headers.
- Print the table rows.
6.1. Test forNULLvalues usingis_null.
6.2. Output the text"NULL"forNULLvalues.
The following is the PHP code for the steps above.
<head>
<meta charset="utf-8">
<title>Describe Table with mysqli_query</title>
<style>
body { display: grid; justify-content: center; align-items: center; height: 100vh; }
table { border-collapse: collapse; width: 20em; }
table,th, td { border: 1px dashed #1a1a1a; }
td,th { padding: 0.5em; }
</style>
</head>
<body>
<main>
<?php
// The username is root
// and the password is empty.
$connection_string = new mysqli("localhost", "root", "", "user_details");
// getting all the rows from the query
$table_bio_data = $connection_string->query("DESC bio_data")->fetch_all(MYSQLI_ASSOC);
// getting keys from the first row
$header = array_keys(reset($table_bio_data));
// Print the table
echo "<table>";
// Print the table headers
echo "<tr>";
foreach ($header as $value) {
echo "<th align='left'>$value</th>";
}
echo "</tr>";
// Print the table rows
foreach ($table_bio_data as $row) {
echo "<tr>";
foreach ($row as $value) {
if (is_null($value)) {
echo "<td>NULL</td>";
} else {
echo "<td>$value</td>";
}
}
echo "</tr>";
}
echo "</table>";
?>
</main>
</body>
Output:
Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.
LinkedIn