I want to output all tables in a MySQL database, 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?
-
you can query the information schema tabels to get all of this dev.mysql.com/doc/refman/5.0/en/information-schema.htmluser557846– user5578462011-08-10 03:20:02 +00:00Commented Aug 10, 2011 at 3:20
Add a comment
|
4 Answers
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;
?>
3 Comments
norris-md
@Thatfuzzbean I updated the code, but look at us.php.net/manual/en/ref.mysql.php and pay notice to the mysql_list_dbs, mysql_list_tables, and mysql_list_fields function to come up with a cleaner approach.
TRiG
Really?
mysql functions? You do know they're depricated, right?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
1 Comment
Sqoo
SELECT * FROM INFORMATION_SCHEMA.TABLES ?
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>';
}
}
Comments
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;
};
