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>";

<?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.