Build HTML Tables From MySQL Tables with PHP

By  on  

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!

Recent Features

Incredible Demos

  • By
    Chris Coyier’s Favorite CodePen Demos IV

    Did you know you can triple-heart things on CodePen? We’ve had that little not-so-hidden feature forever. You can click that little heart button on any Pen (or Project, Collection, or Post) on CodePen to show the creator a little love, but you can click it again...

  • By
    9 Mind-Blowing WebGL Demos

    As much as developers now loathe Flash, we're still playing a bit of catch up to natively duplicate the animation capabilities that Adobe's old technology provided us.  Of course we have canvas, an awesome technology, one which I highlighted 9 mind-blowing demos.  Another technology available...

Discussion

  1. Tables? Really?

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

  3. Ahmed

    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?

    • Richard Rosenthal

      td = table data

  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

    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 '';
    	}
    
    • alex watts

      @Gregorio Espadas your code is EXACTLY like the code submitted by O.P., and I’m receiving the “cannot show columns” error, yet you’ve made no changes that should have fixed anything. The section you posted here also comes after the attempt to query columns, so I don’t know what it would have to do with the error both myself and @Wililam Rouse encountered. Elaboration?

    • @alex watts, there is a way to switch it. Use the code here. Remove the table name using the ‘echo “Table: $table”‘ (I may be wrong). I rely on this code every day, and it works beautifully.

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

    Complete code here:

    http://pastebin.com/cVV7rFGt

    • alex watts

      @Gregorio How is your code any different than what the OP put up? I’m receiving the same error as @William and don’t really see how looking at what you have would have lead him to change anything or for the code to start working.

      Please help! ^_^

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

  11. Greg Alexander

    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

    excelent! David What happened with mootools ?

  15. Markus

    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

    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

    Its nice, Good work.

  18. Blaine

    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

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

  20. Francisco

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

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

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

  24. Ash

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

  25. Faron

    *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

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

  27. Bruno Nogueira

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

  28. Aijay

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

  29. afzaal

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

    Thank you for this!

  31. alex watts

    @Gregorio How is your code any different than what the OP put up? I’m receiving the same error as @William and don’t really see how looking at what you have would have lead him to change anything or for the code to start working.

    Please help! ^_^

  32. Haig

    I am currently using the PHP script to insert database data into my table. However I was wondering if you have a similar PHP script inserting database data into a Flexbox column since Flexbox do not have tables. They are using columns total calculation so when the total of columns reaches 100% then new column is created on the next row( they do not have rows ). Every 100% total creates new one. The columns could be one, two, three or more as long as the total do not exceed 100%. Any idea how I can accomplish this by using something similar like your PHP script for tables.

  33. Carsten

    Never echo out html. It’s ugly and don’t follow the principle of separation of view and model.

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!