PHP Tutorial: Print All MySQL Database Tables & Fields

Comments 17 Standard 

<?php
/****************
* File: displaytables.php
* Date: 1.13.2009
* Author: design1online.com, LLC
* Purpose: display all table structure for a specific database
****************/

//connection variables
$host = "localhost";
$database = "your_db_name";
$user = "your_username";
$pass = "your_pass";

//connection to the database
mysql_connect($host, $user, $pass)
or die ('cannot connect to the database: ' . mysql_error());

//select the database
mysql_select_db($database)
or die ('cannot select database: ' . mysql_error());

//loop to show all the tables and fields
$loop = mysql_query("SHOW tables FROM $database")
or die ('cannot select tables');

while($table = mysql_fetch_array($loop))
{

    echo "
        <table cellpadding=\"2\" cellspacing=\"2\" border=\"0\" width=\"75%\">
            <tr bgcolor=\"#666666\">
                <td colspan=\"5\" align=\"center\"><b><font color=\"#FFFFFF\">" . $table[0] . "</font></td>
            </tr>
            <tr>
                <td>Field</td>
                <td>Type</td>
                <td>Key</td>
                <td>Default</td>
                <td>Extra</td>
            </tr>";

    $i = 0; //row counter
    $row = mysql_query("SHOW columns FROM " . $table[0])
    or die ('cannot select table fields');

    while ($col = mysql_fetch_array($row))
    {
        echo "<tr";

        if ($i % 2 == 0)
            echo " bgcolor=\"#CCCCCC\"";

        echo ">
            <td>" . $col[0] . "</td>
            <td>" . $col[1] . "</td>
            <td>" . $col[2] . "</td>
            <td>" . $col[3] . "</td>
            <td>" . $col[4] . "</td>
        </tr>";

        $i++;
    } //end row loop

    echo "</table><br/><br/>";
} //end table loop
?>

17 thoughts on “PHP Tutorial: Print All MySQL Database Tables & Fields”

  1. Hi, Nice one. thanks. And with your permission i can get this details from my site. And give your reference link for this url.

    Are you giving permission to do this. I have no more english language.

    thanks,
    J.

  3. There is an additional column that can be shown. The updated listing is shown here.

    /*
    +………………………………………..+
    : show table structure :
    +………………………………………..+ */
    $database = “test_db”;
    $loop = mysqli_query($db, “SHOW tables FROM $database”) or die (‘cannot select tables’);

    while($table = mysqli_fetch_array( $loop)) {
    echo ”

    ” . $table[0] . ” table structure

    Field
    Type : Length
    Key
    Index
    Default
    Extra
    “;

    $i = 0; //row counter
    $row = mysqli_query($db, “SHOW columns FROM ” . $table[0])
    or die (‘cannot select table fields’);

    while ($col = mysqli_fetch_array($row)) {
    echo ”
    ” . $col[0] . ”
    ” . $col[1] . ”
    ” . $col[2] . ”
    ” . $col[3] . ”
    ” . $col[4] . ”
    ” . $col[5] . ”
    “;

    $i++;
    } //end row loop

    echo “”;
    } //end table loop

  5. hi,
    I was just wandering,
    how many columns can we add?
    how does the loop knows from $col ?

    thx in adb=vance,

  6. You can add up to count($col) columns to the list. If you always want to print all of them you can change the code inside of the while ($col = mysql_fetch_array($row)) to the following:


    while ($col = mysql_fetch_array($row))
    {
    echo "<tr>";

    //print the whole array returned in $col
    for ($j = 0; $j < count($col); $j++)
    echo "<td>{$col[$j]}</td>";

    echo "</tr>";

    $i++;
    } //end row loop

  8. That’s a great, cute script ! No need to go into cPanel / phpadmin anymore ! But now; Do you have any sample to print certain db-field contents just like an address book, such as this ?:

    Name Surname Tel.no. e-mail address
    ————————————————————————————
    Peter Baker 0123 45678 peter&baker.com

    The script that I “created” prints it all, but not formatted, with certain distance between the fields. I don’t find a way how to format the headline and then underneath the content of the db equally. Sorry, PHP knows me perhaps, but I don’t know much about PHP….

    Thanks for any advise,if you have

  14. Hola! I’ve been reading your web site for a long time now and finally got the bravery to go ahead and give
    you a shout out from Atascocita Texas! Just wanted to tell you keep up the good work!

  16. Hi I am absolutely loving this script it is very close to a small personal project I have been work on,
    it is also the first script when I have copied it into one of my test pages it actually works instead of setting the HTTP error 500,
    it feels fitting I end up here as your site is a lot bout game design,
    I am trying to learn more in PHP I own my own domain and I provide a sub domain to a online gaming community its a group of volunteers that host live online back gammon and cribbage tournaments,
    so I am attempting to create a tool for them as to score keep on multiple mini tournaments at once,
    so I need to have a text type input box to add a player name (varchar) once submitted this needs to also set them a score of 0 (this section even I can setup) then I need to be able to output the list of names and relative scores into a nice table with something like a check box to the right corresponding to each row,
    when checked 1 point is added to that record
    finished off with a page refresh to update the scores in the table
    I know I don’t ask for much but I feel as I am banging my head against a solid wall trying all the ways I can find online

  17. Hi Michael,

    Thanks for your comment. I have some great tutorials about setting up a database and then using a form to add records to the database in my Pits of Doom game tutorial. It also teaches you how to do incremental development on a game as you’re learning to program at the same time. Hopefully that will be a big help for you if you follow the tutorials and it’s open source so you can use any of the code in your own project. Best of luck to you!

Leave a comment