Show MySQL tables, column names, types and more with PHP

Showing a MySQL database tables with their columns, type, keys and more is done using the MySQL SHOW syntax. For the tables SHOW TABLES is used whilst to get the details about the columns for individual tables SHOW COLUMNS.

First step is creating the PDO MySQL connection and then defining the database that the information will be retrieved from

<?php
$db = new PDO('mysql:host=localhost;dbname=DBNAME;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$database = 'users';//Database
$show_tables = $db->prepare("SHOW TABLES;");
$show_tables->execute();
while ($data = $show_tables->fetch(PDO::FETCH_ASSOC)) {
    $table = $data["Tables_in_$database"];//Table name
    .....

This will loop through all the tables in the database user

Next another query gets built into this loop that goes through each of the tables getting the column names, types, keys, defaults and more

<?php
$show_cols = $db->prepare("SHOW COLUMNS FROM $table;");
$show_cols->execute();
while ($col = $show_cols->fetch(PDO::FETCH_ASSOC)) {
   $name = $col['Field'];
   .....

View what gets returned from the show columns command here.

Finally getting creating an output and making its readability somewhat decent can be seen like this WordPress database with the full code below the image;

<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>DB design</title>
    <style>
        body {
            background-color: #313233;
            color: #e2e3e5;
            font-family: Verdana, Geneva, sans-serif;
            font-size: 0.95rem;
        }

        .table-name {
            font-size: 1.2rem;
            margin-bottom: 0.3rem;
        }

        .db-table {
            margin-left: 0.5rem;
        }

        .type {
            color: #abccff;
        }

        .null {
            color: #ffb0a5;
            font-style: italic;
        }

        .key {
            color: #dcffac;
        }

        .default {
            color: #9cffeb;
        }

        .extra {
            color: #c092ff;
        }
    </style>
</head>
<body>
<?php
$db = new PDO('mysql:host=localhost;dbname=DBNAME;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$database = 'users';//Set database name here
$show_tables = $db->prepare("SHOW TABLES;");
$show_tables->execute();
while ($data = $show_tables->fetch(PDO::FETCH_ASSOC)) {
    $table = $data["Tables_in_$database"];
    echo "<h3 class='table-name'>$table</h3>";
    $show_cols = $db->prepare("SHOW COLUMNS FROM $table;");
    $show_cols->execute();
    while ($col = $show_cols->fetch(PDO::FETCH_ASSOC)) {
        if ($col['Null'] == 'YES') {
            $is_null = 'Is NULL';
        } else {
            $is_null = 'Not NULL';
        }
        $key = $col['Key'];
        if ($key == 'PRI') {
            $key_type = 'Primary key';
        } elseif ($key == 'UNI') {
            $key_type = 'Unique key';
        } elseif ($key == 'MUL') {
            $key_type = 'Multi key';
        } else {
            $key_type = '';//No key
        }
        echo "<div class='db-table'>
        <span class='name'>" . $col['Field'] . "</span> <span class='type'>" . $col['Type'] . "</span> <span class='null'>$is_null</span> 
        <span class='key'>$key_type</span> <span class='default'>" . $col['Default'] . "</span> <span class='extra'>" . $col['Extra'] . "</span>
        </div>";
    }
}
?>
</body>
</html>