Build HTML Tables From MySQL Tables with PHP

Written by David Walsh on July 21, 2010 · 32 Comments

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!

Comments

  1. Tables? Really?

  2. @Tanner: Tables for tabular data; yes.

  3. 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

  4. Yeah, dealing with those people is frustrating. Grow up and realize tables aren’t the plague.

  5. 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?

  6. 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

  7. tampe125 July 22, 2010

    hi David, wellcome to the dark side of server coding :D

    (P.S. we have cookies :P)

  8. 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 ”;
    }

  9. Duh! HTML tags were remove in my previous comment.

    Complete code here:

    http://pastebin.com/cVV7rFGt

  10. @Gregorio Espadas: Much better Greqorio, Thanks!

  11. Greg Alexander July 23, 2010

    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!

  12. 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.)

  13. @Tanner: WTF is hard about editing a table? I don’t see the mess at all…

  14. Fanatastic1 August 3, 2010

    excelent! David What happened with mootools ?

  15. 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.

  16. dominicd October 13, 2010

    I have noticed while mysql is outdated many professional developers still depend on outdated mysql rather than using mysqli which is more secure and faster. As Markus said at least PDO. otherwise goodwork

  17. Ramen Dey October 19, 2010

    Its nice, Good work.

  18. You might try and go an extra step. Upon load show databases. Click a database and show tables. Click a table and you could display 10 rows and the structure. May need some breadcrumbs at this point.

  19. Tables are just a tool people. There are instances where they should be used, but this is not for design.

    http://www.serebii.net/index2.shtml

    That website does a splendid job using tables they way they should be used.

    • Richard Rosenthal October 31, 2011

      That website uses a tabled layout, which is not exactly recommended.

  20. Francisco February 23, 2012

    Hey guys, Im totally new to PHP but this is exactly what I need. What sort of privilege do you guys give to a user that only wants to fetch data to display (as above) ? Any help would be great!

  21. Not so hard to construct.. ;)
    Any ways thanks ;)

  22. Hey what can i do if i dont want to show all of the coloumns…
    Like what if i dont wanna show Column of field and type

  23. This is nice. How would you go about showing the data in the tables in place of the table setup.

  24. THANK YOU SO MUCH…. U SAVED MY LIFE….

  25. *nodding*

    yea, man! Kudos! This enables me to have my webapp to auto generate the class and self-select from database and initiate the query on its own without my having to go to phpMyAdmin.

    You rock, dude!

  26. Uttam Dhar February 11, 2013

    I can’t display by html table on Mysql Data.can any one pls help me

  27. Bruno Nogueira April 30, 2013

    Great piece of script, works just fine! And tables will always exist, look the Microsoft Excel for example, strong and going on :)

  28. Thanks for this great piece. It helped me solve an issue with displaying data using html table within seconds.

  29. All Event are Shown What You want

    <?php
     error_reporting(E_STRICT);
    $host="127.0.0.1";
    $username="root";
    $password="";
     $db="home";
    mysql_connect("$host","$username","$password") or die("cannot connect");
    mysql_select_db("$db") or die("can not select database");
    	        $sql = "SELECT * FROM peson";
    	        $result=mysql_query($sql);
                            echo "";
                     echo         "
    ID
    Event Name
    Starting Date
    Ending Date
    About Event
     Action
    ";
                            while($row = mysql_fetch_array($result)){
                              echo"" .$row['id'] .""; echo "".$row['eventn'].""; echo"".$row['startingd'].""; echo"".$row['endingd'].""; echo"". $row['aboute'] . ""; echo  "update/delete";
                            }
                            echo"";
                            mysql_close();
    					/*if($result)
    						{
    							die('Data has been Entered'.mysql_error());
    							}
    						else
    							{
    							echo "Did not Entered";
    							} */
    							?>
    
  30. Thank you for this!

Be Heard

Tip: Wrap your code in <pre> tags or link to a GitHub Gist!

Use Code Editor
Older
Multiple File Upload Input
Newer
HTML5: Wrap Block-Level Elements with A's