Treehouse

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!

ydkjs-6.png

Recent Features

Incredible Demos

  • MooTools dwCheckboxes Plugin

    Update / Fix: The checkboxes will no longer toggle when the "mouseup" event doesn't occur on a checkbox. Every morning I wake up to a bunch of emails in my Gmail inbox that I delete without reading. I end up clicking so many damn checkboxes...

  • CSS @supports

    Feature detection via JavaScript is a client side best practice and for all the right reasons, but unfortunately that same functionality hasn't been available within CSS.  What we end up doing is repeating the same properties multiple times with each browser prefix.  Yuck.  Another thing we...

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 '';
    	}
    
  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

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

    Hi, i did this for retrive data from mysql via php and initialize that table with dataTables all with 1 line of code

    /**
         * Create dinamic table
         *
         * @param string $dataTableName TableID for DataTable
         * @param string $tableName MySql Table Name
         * @param array $a_colName HTML Table Column Title
         * @param array $a_fields MySql Fields Name
         */
        function dataTable($dataTableName, $tableName, $a_colName, $a_fields){
            mysql_connect(“localhost”, “root”, “”);
            mysql_select_db(“db”);
            if( count($a_colName) != count($a_fields) ){
                echo “Coulmn count is different from fields...what you forgot ?”;
                exit();
            }
            $numCol = count($a_colName);
            $q = “SELECT id, “;
            foreach ($a_fields AS $campo){
                $q .= $campo . “, “;
            }
            $q = substr($q, 0, -2);
            $q .= ” FROM $tableName“;
            $result = mysql_query($q);
            if(!$result) echo mysql_error ();
            echo “”;
            echo “”;
            foreach ($a_colName AS $col){
                    echo “” . $col . “” ;
                }
           echo “”;
            $count = mysql_affected_rows();
            if($count != 0) {
                while ( $data = mysql_fetch_assoc($result) ){
                    echo ‘ ‘;
                    foreach ($a_fields AS $campo){
                        echo “” . $data[$campo] . “” ;
                    }
                 echo ‘’;
                }
            } else {
                echo “”;
            }
            echo “”;
            echo ‘  $(document).ready(function() { $(“#’.$dataTableName .‘”).dataTable(); });’;
        }
    

    Ofc can be optimized…but it work!

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