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!

example_heatmap

Field	Type	Null	Key	Default	Extra
click_id	mediumint(6)	NO	PRI		auto_increment
zone	varchar(60)	NO
x	smallint(5)	NO		0
y	smallint(5)	NO		0
date_clicked	datetime	NO

example_periodical_ajax

Field	Type	Null	Key	Default	Extra
entry_id	int(10) unsigned	NO	PRI		auto_increment
user	varchar(50)	NO
message	varchar(140)	NO

nettuts1

Field	Type	Null	Key	Default	Extra
status_id	int(10) unsigned	NO	PRI		auto_increment
user_id	int(5)	NO
status	varchar(150)	NO
date_set	datetime	NO

pastebin

Field	Type	Null	Key	Default	Extra
pid	int(8)	NO	PRI		auto_increment
poster	varchar(150)	NO
posted	timestamp	NO		CURRENT_TIMESTAMP
domain	varchar(40)	NO		paste
format	varchar(50)	NO
code	longtext	NO
codefmt	longtext	NO
codecss	longtext	NO
parent_pid	int(8)	NO
expires	timestamp	NO		0000-00-00 00:00:00
expiry_flag	varchar(20)	NO

recent

Field	Type	Null	Key	Default	Extra
something	varchar(200)	NO
domain	varchar(40)	NO
seq_no	int(8)	NO
pid	int(8)	NO

shows

Field	Type	Null	Key	Default	Extra
show_id	smallint(6) unsigned	NO	PRI		auto_increment
date_starts	date	NO
stime	varchar(50)	NO
venue	varchar(150)	NO
url	varchar(255)	NO
city	varchar(50)	NO
state	varchar(2)	NO

test_table

Field	Type	Null	Key	Default	Extra
id	tinyint(3) unsigned	NO	PRI		auto_increment
title	varchar(50)	NO
sort_order	tinyint(3)	NO

The CSS

Copy Code

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

Copy Code

/* 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.

Nothing 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!