SQL: Displaying Tables from Database

The SQL DESCRIBE command places table columns, as well as the format and other parameters of the column in the table. It is used in conjunction with SHOW TABLES, which returns a list of tables.

Using the algorithm, you can view all columns and see the database structure, check the format of the fields.

For example, a WordPress configuration file:

<?php
define('DB_NAME', 'theme');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');
$table_prefix  = 'WP_';
?>

It includes a database connection:

$base = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die("MySQL database '".DB_NAME."' not accessible.<br>\n");  

mysqli_select_db($base, DB_NAME)
or die("Enable to select ".DB_NAME." database<br>\n");

SHOW TABLE command

First we get a list of tables:

$results = mysqli_query($base, "SHOW TABLES");
if($results == false)   die("Empty database");

$tables = array();
while($arr = mysqli_fetch_array($results))
{
  array_push($tables, $arr['0']);
}

Table names are added to the $ tables table.

DESCRIBE command

For each table, you can get the name of the columns:

foreach($tables as $table)
{
  $results = mysqli_query($base, "DESCRIBE $table");

  echo "TABLE $table <br>";

  while($arr = mysqli_fetch_array($results))
  {
    echo "Nom ", $arr['0'], "<br>";
    echo "Type ", $arr['1'], "<br>";
    echo "Peut être null ", $arr['2'], "<br>";
    echo "Clé ", $arr['3'], "<br>";
  }
}

"PRI" is a shortcut to PRIMARY KEY.

For a more readable presentation, the table in the full file will be used.

View full code