This simple tutorial will show you how to output the details of a MySQL database to a HTML table using PHP in just a few simple steps.
First of you’ll need my formatfilesize() function; for this script to work you will obviously need to connect to your MySQL server and select the database you wish to output. Then run this query:
Run the Query
<?php
$query = “SHOW TABLE STATUS”;
$result = mysql_query( $query );
?>
The above query gives us access to all the table names in this database. We’ll need these names later on. Now set up a HTML table with a columns for each of the values we will output:
Start off the Table
<table>
<tr>
<td><strong>Table Name</strong></td>
<td><strong>Rows</strong></td>
<td><strong>Average Row Length</strong></td>
<td><strong>Data Length</strong></td>
<td><strong>Index Length</strong></td>
<td><strong>Total Length</strong></td>
<td><strong>Update Time</strong></td>
</tr>
Now comes the important part – we run an EXPLAIN query on each table – this returns all the variables we need:
Output the MySQL Details
<?php
$totalsize = 0;
while( $row = mysql_fetch_array( $result ) ) {
extract( $row );
$title = “”;
$re = mysql_query( “EXPLAIN $Name” );
$totallength = $Data_length + $Index_length;
$totalsize += $totallength;
$totallength = “<strong>” . formatfilesize( $totallength ) . “</strong>”;
$Data_length = formatfilesize( $Data_length );
$Index_length = formatfilesize( $Index_length );
while( $r = mysql_fetch_array( $re ) ) {
$title .= $r[ "Field" ] . ” [" . $r[ "Type" ] . “]\n”;
}
echo ‘<tr title=” ‘ . $title . ‘”>’;
echo ” <td>$Name</td>
<td>$Rows</td>
<td>$Avg_row_length</td>
<td>$Data_length</td>
<td>$Index_length</td>
<td>$totallength</td>
<td>$Update_time</td>”;
echo ‘</tr>’;
}
?>
All that remains is to close the table:
Close the Table
</table>