Describe Database Table With Mysqli_query in PHP

  1. Set Up the Test Database
  2. Describe the 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 Shell on the right side of the dialog window. This will launch the console.
  • Type mysql -u root -p and 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.

  1. Connect to the database.
  2. Use the DESCRIBE command on the bio_data table using the OOP version of mysqli_query.
  3. Fetch the result using fetch_all.
  4. Get the keys for the first row. The first row will be the table header.
  5. Print the table headers.
  6. Print the table rows. 6.1. Test for NULL values using is_null. 6.2. Output the text "NULL" for NULL values.

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:

Description of a MySQL table in Firefox 100

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PHP MySQL

  • Insert Special Characters Into a Database in PHP
  • Execute Multiple MySQL Queries in PHP
  • Set Up a Search System With PHP and MySQL