Build HTML Tables From MySQL Tables with PHP

I was recently completing a project which required that I build a series of HTML tables which would represent all of the tables within a MySQL database.  I didn't have anything created but after a few minutes I had exactly what I needed. Hopefully this helps you out!

The CSS

table.db-table     { border-right:1px solid #ccc; border-bottom:1px solid #ccc; }

table.db-table th  { background:#eee; padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }

table.db-table td  { padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }

The CSS I'm styling the table with is as basic as it gets — style as you wish!

The PHP / MySQL

/* connect to the db */ 

$connection = mysql_connect('localhost','username','password');

mysql_select_db('my_db',$connection);

/* show tables */ 

$result = mysql_query('SHOW TABLES',$connection) or die('cannot show tables');

while($tableName = mysql_fetch_row($result)) { 

$table = $tableName[0];

echo '<h3>',$table,'</h3>';

$result2 = mysql_query('SHOW COLUMNS FROM '.$table) or die('cannot show columns from '.$table);

if(mysql_num_rows($result2)) { 

  echo '<table cellpadding="0" cellspacing="0" class="db-table">';

  echo '<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default<th>Extra</th></tr>';

  while($row2 = mysql_fetch_row($result2)) { 

    echo '<tr>';

    foreach($row2 as $key=>$value) { 

      echo '<td>',$value,'</td>';

    } 

    echo '</tr>';

  } 

  echo '</table><br />';

} 

}

The first step in the process is accessing all of the tables within the database.  Once all tables have been fetched, the next step is to loops through the array of tables we receive and, for each table, build a new HTML table with column information.


othing groundbreaking but surely has use.  I've also written a blog post about backing up your MySQL database with PHP titled Backup Your MySQL Database Using PHP; check that out if you'd prefer to backup your databse information in SQL format!