Output MySQL Table Details

>> Output your MySQL tables to a HTML table

RESOURCE ADDED ON TUESDAY 18 JULY 2006, 11:56:01

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:

Code: Run the Query

<?php

$query = "SHOW TABLE STATUS";
$result = mysql_query( $query );

?>

Downloads: 246

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:

Code: Start off the Table

<table>
 <tr>
 <td>Table Name</td>
 <td>Rows</td>
 <td>Average Row Length</td>
 <td>Data Length</td>
 <td>Index Length</td>
 <td>Total Length</td>
 <td>Update Time</td>
 </tr>

Downloads: 165

Now comes the important part - we run an EXPLAIN query on each table - this returns all the variables we need:

Code: 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 = "" . formatfilesize( $totallength ) . "";
 $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>';

 }

?>

Downloads: 290

All that remains is to close the table:

Code: Close the Table

</table>

Downloads: 56

This resource has been viewed 6790 times.

Back to Resources

Back to Tutorials

Back to Web Severside

Support Dr Quincy

These resources are provided free of charge. If, however, you would like to make a donation to help finance these resources, no matter how big or small, please use the PayPal button below.

Credits

This resource was written by Tim Bennett Bsc (Hons). Tim Bennett has a First Class Degree in Multimedia Systems Computing from Leeds Metropolitan University. He works as a freelance multimedia developer under the company name Texelate creating free and commissioned high-quality multimedia solutions. Find out more by visiting the portfolio section and viewing his blog. If you'd like to request a resource get in touch!