Tool for showing MySQL database structure

@NogDog

Copy linkTweet thisAlerts:

Dec 10.2005

Here’s a PHP script I finished up today for displaying the structure of all tables within a specified database. Hopefully some of you may find it useful (and bug-free, I hope).

Enjoy.

[code=php]
<?php
/*
* CLASS: ShowTables
* PURPOSE: For specified database, shows structure of each table
* NOTES: Expects the following POST data:
* submit: {any value}
* host: database host
* user: database user name
* password: database user password
* database: database name
* HISTORY:
* CREATED: 2005/12/10 by Charles Reace
* copyright (c) 2005 by Charles Reace, www.charles-reace.com
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
class ShowTables
{
// ATTRIBUTES:

var $error = “”; # error message
var $connx = “”; # mysql connection ID
var $tables = array();

// METHODS:

// constructor:
function ShowTables()
{
if(empty($_POST[‘submit’]))
{
return(null); // do nothing if form not submitted.
}
if($this->dbConnect())
{
$this->cleanPost();
if($this->getTables())
{
echo “<h2>Table Descriptions for Database ‘{$_POST[‘database’]}'</h2>n”;
if(! $this->describe())
{
echo “<p class=’error’>{$this->error}</p>n”;
}
}
else
{
echo “<p class=’error’>{$this->error}</p>n”;
}
}
else
{
echo “<p class=’error’>{$this->error}</p>n”;
}
} // end constructor

/*
* void cleanPost(void)
* PURPOSE: clean up Post data to make safe for use in queries
*/
function cleanPost()
{
foreach($_POST as $key => $val)
{
if(get_magic_quotes_gpc())
{
$val = stripslashes($val);
}
$_POST[$key] = mysql_real_escape_string($val);
}
}
/*
* bool dbConnect(void)
* PURPOSE: connect to mysql and select db
* RETURNS: true on success, else false, saves error message to $this->error
*/
function dbConnect()
{
extract($_POST, EXTR_PREFIX_ALL, “DB”);
$connx = @mysql_connect($DB_host, $DB_user, $DB_password);
if($connx)
{
$result = @mysql_select_db($DB_database);
if(!$result)
{
$this->error = “Unable to select database $DB_database.”;
mysql_close($connx);
return(FALSE);
}
$this->connx = $connx;
}
else
{
$this->error = “Unable to connect to MySQL. Check values for host, user, and password.”;
return(FALSE);
}
return(TRUE);
} // end dbConnect()

/*
* bool getTables(void)
* PURPOSE: get table names db, store them in $this->tables
* RETURNS: true on success, else false, stores error msg in $this->error
*/
function getTables()
{
$result = mysql_query(“SHOW TABLES”, $this->connx);
if($result)
{
while($row = mysql_fetch_row($result))
{
$this->tables[] = $row[0];
}
return(TRUE);
}
else
{
$this->error = “Problem getting list of tables from DB. – “.mysql_error();
return(FALSE);
}
} // end getTables()

/*
* bool describe(void)
* PURPOSE: run the MySQL DESCRIBE query on each table
* RETURNS: true on success, else false, stores error msg in $this->error
*/
function describe()
{
foreach($this->tables as $key => $table)
{
echo “<h3>Table: $table</h3>n”;
echo “<table>n”;
$thFlag = FALSE; // indicates whether column headers have been output yet
$result = @mysql_query(“DESCRIBE `$table`”);
if(!$result)
{
$this->error = “Describe query failed. – ” . mysql_error();
return(FALSE);
}
else
{
while($row = mysql_fetch_assoc($result))
{
if(!$thFlag)
{
$thFlag = TRUE;
echo “<tr>”;
foreach($row as $key => $val)
{
echo “<th>$key</th>”;
}
echo “</tr>n”;
}
echo “<tr>”;
foreach($row as $val)
{
echo “<td>$val</td>”;
}
echo “</tr>n”;
}
}
echo “</table>n”;
}
return(TRUE);
} // end describe()

} // end class ShowTables
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd”>
<html lang=’en’>
<head>
<META HTTP-EQUIV=’Content-Type’ CONTENT=’text/html; charset=ISO-8859-1′>
<title>Page title</title>
<style type=”text/css”>
<!–
body {
font: medium arial, sans-serif;
background-color: #e6f6ff;
color: black;
margin: 0;
padding: 10px 20px;
}
fieldset {
width: 20em;
}
legend {
font-weight: bold;
}
label {
float: left;
clear: left;
width: 5em;
text-align: right;
margin: 0.2em 0.5em;
padding: 0;
}
input {
float: left;
clear: right;
margin: 0.2em 0.5em;
padding: 0;
}
input[type=”submit”] {
clear: both;
margin-left: 8em;
}
table {
border-collapse: collapse;
border: solid 2px black;
}
th, td {
padding: 2px 4px;
font-size: 85%;
border: solid 1px black;
}
th {
border-bottom: solid 2px black;
background-color: #ddd;
}
h3 {
font-size: medium;
margin: 1em 0 0.5em 0;
padding: 0.25em;
}
.error {
background-color: inherit;
color: #c03;
}
#colophon {
font-size: small;
text-align: center;
margin-top: 2em;
padding-top: 1em;
border-top: solid 1px #ccc;
}
–>
</style>
</head>
<body>
<h1>Get MySQL Database Structure</h1>
<form action=”<?php echo $_SERVER[‘PHP_SELF’]; ?>” method=”post”>
<fieldset>
<legend>Get DB Structure</legend>
<div>
<label for=”host”>DB Host:</label>
<input type=”text” name=”host” id=”host” value=”localhost”
size=”24″ maxlength=”32″>
<label for=”user”>DB User:</label>
<input type=”text” name=”user” id=”user” size=”16″ maxlength=”16″>
<label for=”password”>Password:</label>
<input type=”password” name=”password” id=”password” size=”16″ maxlength=”16″>
<label for=”database”>Database:</label>
<input type=”text” name=”database” id=”database” size=”24″ maxlength=”32″>
<input type=”submit” name=”submit” value=”Show Tables”>
</div>
</fieldset>
</form>
<?php
$show = new ShowTables;
?>
<p id=”colophon”>
Copyright &copy; 2005 by Charles Reace,
<a href=”http://www.charles-reace.com”>www.charles-reace.com</a>,<br>
This software may be freely distributed under the terms of the
<a href=”http://www.gnu.org/copyleft/gpl.html#SEC1″>GNU Public License</a>.</p>
</p>
</body>
</html>
[/code]

Also in #PHP _↴