Can I display all tables and data in a MySQL database?

I want to output all tables in a MySQL dat开发者_开发知识库abase, along with all the records in each of the tables, as a single web page. I can do this for one table using the SELECT function, but want to do this for a rapidly changing database. New tables will be added and deleted over time, so I want a generic function which will display all data from all tables in a database. Is this possible?

This will list all tables in a database and order them by their table name and ordinal position. I have omitted some of the columns that you may not need, but double check the column selections.

Ok... you are getting more than I would normal give away, but try this and see if it is what you are looking for.

<?php
$sql = "SELECT `TABLE_SCHEMA`,`TABLE_NAME`, `COLUMN_NAME`, `ORDINAL_POSITION`,
            `COLUMN_DEFAULT`, `IS_NULLABLE`, `DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH`,
            `CHARACTER_SET_NAME`, `COLLATION_NAME`, `COLUMN_TYPE`, `COLUMN_KEY`
        FROM INFORMATION_SCHEMA.COLUMNS
        ORDER BY `TABLE_SCHEMA`,`TABLE_NAME`,`ORDINAL_POSITION` ASC;";

mysql_connect($host, $username, $password) or die();
mysql_select_db($database) or die();
$result = mysql_query($sql);

$table_top = "<table cellpadding=\"2\" border=\"1\"><tr><th>Schema</th><th>Table</th><th>Column</th><th>Position</th><th>Key</th><th>Type</th><th>Max Length</th></tr>";
$table_change_row = "<tr><th bgcolor=\"black\"></th><th>Table</th><th>Column</th><th>Position</th><th>Key</th><th>Type</th><th>Max Length</th></tr>";
$table_bottom = "</table>";

$current_schema = '';
$current_table = '';

echo $table_top;

while ($row = mysql_fetch_object($result)) {
    if ($current_schema!=$row->TABLE_SCHEMA && !empty($current_schema))
    {
            echo $table_bottom;
            echo $table_top;
    }

    if ($current_table!=$row->TABLE_NAME)
    {
            echo $table_change_row;
    }

    $current_schema = $row->TABLE_SCHEMA;
    $current_table = $row->TABLE_NAME;
    echo "
    <tr>
            <td>$row->TABLE_SCHEMA</td>
            <td>$row->TABLE_NAME</td>
            <td>$row->COLUMN_NAME</td>
            <td>$row->ORDINAL_POSITION</td>
            <td>$row->COLUMN_KEY</td>
            <td>$row->DATA_TYPE</td>
            <td>$row->CHARACTER_MAXIMUM_LENGTH</td>
    </tr>";
}

echo $table_bottom;
?>

use this query to get all table name in selected database

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

use foreach loop use second loop to get structure detail

DESCRIBE ;

or 
SELECT column_name,column_type,is_nullable,column_key,col  umn_default,extra FROM information_schema.COLUMNS WHERE 
table_name=''

by combination of both using php , you can get all info needed

An alternate approach, which lists things out in a more lexical fashion. Personally, I'd dump everything into an array and iterate over that, but I know that some people prefer to just write it out inline.

$dbName = 'foo';

$res = $mysqliCnx->query('show tables');
if ($res)
{
    while ($row = mysqli_fetch_assoc($res))
    {
        $currTable = $row['Tables_in_'.$dbName];
        echo '<h2>'.$currTable.'</h2><ul>';
        $res2 = $mysqliCnx->query('show columns from '.$currTable);
        while ($fields = mysqli_fetch_assoc($res2))
        {
             echo '<li>'.$row['field'].' - ('.$row['Type'].')</li>';
        }
        echo '</ul>';
    }
}

function db_show_tables() {
  $o=mysql_query("show tables");
  if($o!==false) {
    while($w=mysql_fetch_row($o)) $lista[]=$w[0];
    return $lista;
  };
  return $o;
};