Saturday, June 9, 2012

How to display MySQL table structure in HTML form using PHP

Advertisement:

Hi hungers oops!! programming hungers, today i am posting an interesting article which mainly focuses on "How to display MySQL table structure in HTML using PHP". This is interesting article??, stop it man.This task can be done by anyone. (anyone in the sense any PHP Developer). Yes definetely anyone can do it. Anyone? Ok..If you can concentrate on my question you will be amazed. After seeing the question anyone can say 'simply fetch the mysql table records and display onto the HTML form'. Isn't it? My answer is "It's not." You have mistaken in understanding the client requirement.

Lets see my requirement: I dont want to display my table records in HTML form, rather i want to display MySQL database's table structure like "What's table name, How many columns are ther in the table, What are the datatypes used in the table like wise." If you are confused see the picture below. A famous chinese proverb "A picture is worth a million words". Lets see the million words picture. I am just kidding. Lets see the picture.
MySQL table structure in HTML

Now i hope you understand what i am going to show you. Here you may get one doubt. If i want to see the table structure then i can go to phpMyAdmin and from there i can see the structure of any table by selecting the appropriate database's table then whats the need of this code. Yes definetely, you can see, but provided you can only see one table structure at one time. You can't see all tables at one time. This is the application which shows you all the tables structures.

In this demo i will show you a single table structure from the database. Why i will show you single table structure is because i have only one table in my database. You can try out for a big database which is having multiple tables using this code. You can see multiple tables under single database using the same code. Now lets see the code.

Step 1:
First create a database called "phphunger" in the phpmyadmin. After this create a table called "websites" and paste the following code in your "phphunger" database.

CREATE TABLE IF NOT EXISTS `websites` (
`id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`visits` decimal(10,0) NOT NULL DEFAULT '0',
`about` text NOT NULL,
`type` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `name` (`name`,`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
Step 2: 
Design some CSS Code and place this in <head> section of your php form. See below for CSS Code.

<style type="text/css">
{ border-right:1px solid #ccc; border-bottom:1px solid #ccc; }
{ background:#afb0bd; padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }
{ padding:5px; border-left:1px solid #ccc; border-top:1px solid #ccc; }
</style>

Step 3:
Now the real hero of our application came. Yes the business logic. Copy the below code and paste it onto your PHP form. Now you are done with your task. Now see the result. Hurrah i got it...

<?php
/* Connect to the db */
$connection = mysql_connect('localhost','root','');
mysql_select_db('phphunger',$connection);
/* Show tables */
$result = mysql_query('SHOW TABLES',$connection) or die('Cannot show tables');
/* Loop through tables */
while($tableName = mysql_fetch_row($result)) {
$table = $tableName[0];
echo '<h2>',$table,'</h2>';
$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 />';
}
}
?>

Output:
Structure of mysql table
Now you are done with the task. Now lets see how it fetches and displays in a HTML tabular format.

1. Fetch all of the tables present in the database called "phphunger" using mysql_query() function.
2. Once all the tables have been fetched, then the next step is to loop through the array of tables we receive.
3. For each table we receive show the same received data in the tabular format in HTML form with the column information. That's it. Over.

Now its your turn to say me hi,hello,bye kind of stuff in this comments section. If you really like this article please say thanks. If you don't like this article then curse me. Oops..hey!!! stop stop..please don't curse me. You can give some positive feedback about how to improvise next time. Till then bye...

Did You Enjoy this Article ?

If yes, Then enter your email below to get

more such great articles in your inbox

For FREE !

7 comments:

  1. nice tip for showing table structure..good one..

    ReplyDelete
  2. This one is good... actually i was trying to find some thing like this for my online dbms college project.
    Thanks a ton! :)

    ReplyDelete
  3. Thanks-this works well.
    I was actually looking for info on how to display the data from MySQL table into PHP file (in html table).
    Maybe this code can be modified to do that...

    ReplyDelete
  4. Thanks man. Saved my good time. :-)

    ReplyDelete
  5. Hi, Great tutorial which helped me to put data from my database to website www.teluguinvestor.com . Thanks

    ReplyDelete
  6. 5 star dude, superb!!!

    ReplyDelete

Thank you for reading and commenting...