O'Reilly

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!

Track.js Error Reporting

Upcoming Events

Recent Features

  • Serving Fonts from CDN

    For maximum performance, we all know we must put our assets on CDN (another domain).  Along with those assets are custom web fonts.  Unfortunately custom web fonts via CDN (or any cross-domain font request) don't work in Firefox or Internet Explorer (correctly so, by spec) though...

  • Chris Coyierâs Favorite CodePen Demos II

    Hey everyone! Before we get started, I just want to say it’s damn hard to pick this few favorites on CodePen. Not because, as a co-founder of CodePen, I feel like a dad picking which kid he likes best (RUDE). But because there is just so...

Incredible Demos

  • Hot Effect: MooTools Drag Opacity

    As you should already know, the best visual features of a website are usually held within the most subtle of details. One simple trick that usually makes a big different is the use of opacity and fading. Another awesome MooTools functionality is...

  • CSS Text Overlap

    One of the important functions of CSS is to position elements. Margin, padding, top, left, right, bottom, position, and z-index are just a few of the major players in CSS positioning. By using the above spacing...

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?

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

  32. 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! ^_^

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

Recently on David Walsh Blog

  • Intercept HTTP Requests with Node.js nock

    Unit testing external APIs is difficult no matter what language you do it in.  Hell, working with any external API is scary, if only because you have zero control of networking issues, API changes, and a host of other issues.  But if you do create a service...

  • Introducing Frontend Masters (with Giveaway)

    Hey DWB readers, I'm super happy to sponsor this blog. I've been a long-time reader and fan, since back when David wrote about JavaScript and MooTools back in 2007. ;-) We are in one of the fastest changing, evolving, most lively communities on earth: JavaScript and front-end web...

  • Get Node.js Command Line Arguments with yargs

    Using command line arguments within Node.js apps is par for the course, especially when you're like me and you use JavaScript to code tasks (instead of bash scripts).  Node.js provides process.argv but that doesn't provide a key: value object like you'd expect: Bleh.  If you want to work with a...

  • OâReilly Velocity Conference â New York

    My favorite front-end conference has always been O'Reilly's Velocity Conference because the conference series has focused on one of the most undervalued parts of client side coding:  speed.  So often we're so excited that our JavaScript works that we forget that speed, efficiency, and performance are just as important. The next Velocity...

  • Free Download: Font Bundle Featuring 17 Incredible Typefaces

    The only thing we love more than a good font, is a good free font. So we’ve combed the Web for some of our favorite free fonts, and gathered them here in a single download. You’ll find a variety of useful typefaces, from highly geometric designs...