How to Print MySQL Table Summary using PHP?

The MySQL table has a command Show Table Status which prints a table summary, but it contains lots of information about all tables in the current database (to change database, use ‘use database_name’), which is not so user friendly.

What we want to know is the size of the data and the size of the index, you could use the following PHP script to print such information.

<?php
  require('conn.php'); // connect your database using e.g. $connection = mysqli_connect(...)
  $query = "show table status";
  $result = mysqli_query($connection, $query);
  $ds = 0;
  $is = 0;
  echo "<table style='text-align:left;width:100%'>";
  echo "<tr  style='background:blue;color:yellow;'><th>Name</th><th>Data</th><th>Index</th></tr>";
  function format($c) {
    return (round($c / 1024, 2)) . 'KB';
  }
  while ($row = mysqli_fetch_assoc($result)) {
    echo "<tr><th>" . ($row['Name']) . '</th>';
    echo "<th>" . format($row['Data_length']) . "</th>";
    echo "<th>" . format($row['Index_length']) . "</th>";
    $ds += $row['Data_length'];
    $is += $row['Index_length'];
    echo "</tr>";
  }
  echo "<tr style='background:lightblue;'>";
  echo "<th>". format($ds + $is) . "</th><th>" . format($ds) . "</th><th>" . format($is) . "</th>";
  echo "</tr></table>";

Here is an example of the usage from a typical wordpress database. We format the size using KB. In this way, you could keep track of the table usage from time to time. You can also amend the stylish CSS in the above code to format the output.

mysql-show-table-status-in-php-example

DevOps / Site Reliability Engineering

• How to Clean Up NVM Node Versions Except One?
• Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
• Python/Bash Script to Print the Optimized Parameters for MySQL Servers
• Learn to Manage Your MySQL Database with a Python Script
• A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
• How to Print MySQL Table Summary using PHP?
• Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
• Bash Script to Check, Repair, Optimise and Backup MySQL database
• Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
• MySQL server stopped due of out of memory exception on Ubuntu VPS
• Running Apache Server (PHP + MySQL) on Raspberry PI
• How to Optimise SQL Queries? Quick Tips
• Recovery Models in SQL Server
• Database Optimisation Script in PHP

–EOF (The Ultimate Computing & Technology Blog) —