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.
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!
Discussion
Be Heard!
Share your thoughts with fellow developers of all skill levels! I want to hear from you!
Tables? Really?
@Tanner: Tables for tabular data; yes.
I just don’t get it. Some people are against tables for the heck of looking cool, I believe. Tabular data will always exist, for which there will always be tables, period.
I wish those *cool* web designers would stop saying “Ewwww, tables”
Thanks for sharing this, Dee Doubl-You
Yeah, dealing with those people is frustrating. Grow up and realize tables aren’t the plague.
I always tell people to keep tables out of their sites unless they are displaying tabular data.
But when I have tabular data to display I alway go away from them.
Why? The ability to transform that data into another form later on…. Maybe
But screen readers also wont know it is a table – so that is bad.
I guess I cant explain it – I know when tables are to be used – I just dont like to use them. – Maybe the syntax?
Maybe if I could figure out what td means – t for table and d for… cell? column?
Dave:
When I run your code, I connect to my database and see the database name and the header, but I don’t see the column names and characteristics for each of the column as in your demo.
Do you have some idea of why this might happen?
Thanks
hi David, wellcome to the dark side of server coding :D
(P.S. we have cookies :P)
William, same here. I just reorder some things, and the correct code is:
if(mysql_num_rows($result2)) {
echo ‘FieldTypeNullKeyDefaultExtra’;
while($row2 = mysql_fetch_row($result2)) {
echo ”;
foreach($row2 as $key=>$value) {
echo ”,$value,”;
}
echo ”;
}
echo ”;
}
Duh! HTML tags were remove in my previous comment.
Complete code here:
http://pastebin.com/cVV7rFGt
@Gregorio Espadas: Much better Greqorio, Thanks!
RE “Tables? Really?”
@Tanner Seriously, that is all you have to offer to the coding communitiy is a snobby waste of space remark…… kinda like this one :). David, thanks for the sharing of the knowledge!
Tables aren’t necessary for tabular data. In-fact, you would be better off using an unordered list and some css than a table!
As Adam Meyer stated, one problem with using tables is if you want to go back and edit the data or display it differently… you’ve got a bit of a mess on your hands. Sure, you could simply stylize your tables, but what’s to stop the tables from displaying like poop on different devices?
It all comes down to the individual developer and the purpose behind the project, I guess. Ultimately everyone is entitled to their opinion and will do what they think is best. If you love tables, go for it, no worries. But there are alternatives, some of which are more modern and fun to play with.
(Here’s the kicker: IMO.)
@Tanner: WTF is hard about editing a table? I don’t see the mess at all…
excelent! David What happened with mootools ?
Welcome to the server-side ;) Mootools is about objects and so is server-side programming. So instead of using the old procedural mysql_* interface, you should have a look at PDO.