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>
A drained and empty Kennington reservoir images from a drone in early July 2024. The…
Merrimu Reservoir from drone. Click images to view larger.
Using FTP and PHP to get an array of file details such as size and…
Creating and using Laravel form requests to create cleaner code, separation and reusability for your…
Improving the default Laravel login and register views in such a simple manner but making…
Laravel validation for checking if a field value exists in the database. The validation rule…