[PHP/SQL] Database Structure

talk open source
1

This is a script that displays the structure of your MySQL Database. It gives you lists of all of the databases and then all of the tables within each database and each of the columns within those.

The motivation for this small script came from wanting to find out how you get lists of the databases, tables and table columns on a MySQL server, which this accomplishes well.

The script is broken into two files: the included file contains a function to neatly display the results of the result and the main script runs everything.

Note: If your MySQL has masses of databases and lots of large tables within those, this script could take a long time to run. I personally have only tried this with a freshly installed MySQL server so I can’t say for sure under what circumstances this may lag.

mysql_fetch_table.inc.php

<?

/**
 * Format the result of a MySQL query into a HTML Table
 * @param resource $result	The result of the query
 * @return string $table	The HTML of the table
 */
function mysql_fetch_table($result) {
	/**
	 * Return different table on $result error
	 */
	if (!$result) return "<table><tr><td>Invalid Result</td></tr></table>";
	if (mysql_num_rows($result) == 0) return "<table><tr><td>Empty Set</td></tr></table>";

	mysql_data_seek($result, 0);

	/**
	 * @var array $fields	The fields (columns) of the table
	 * @var array $results	The results (rows) of the table
	 */
	$fields = array();
	$results = array();
	
	// Fetch the first result and get the fields
	$r = mysql_fetch_assoc($result);
	foreach ($r as $key=>$value) array_push($fields, $key);
	array_push($results, $r);

	// Get the result of the results
	while ($r = mysql_fetch_assoc($result)) array_push($results, $r);

	// Generate the HTML table headers
	$HTML = "<table><thead><tr>";
	foreach ($fields as $field) $HTML .= "<th>".htmlentities($field)."</th>";
	$HTML .= "</tr></thead>";

	// Generate the HTML table body
	$HTML .= "<tbody>";
	foreach ($results as $r) {
		$HTML .= "<tr>";
		foreach ($r as $value) $HTML .= "<td>".htmlentities($value)."</td>";
		$HTML .= "</tr>";
	}
	$HTML .= "</tbody></table>";

	mysql_data_seek($result, 0);
	return $HTML;
}

index.php

<html>
	<head>
	<title>MySQL Test</title>
	<style type="text/css">
		body {
			white-space: pre;
		}
		* {
			font-family: "Courier New", Courier, mono;
			font-size: 12px;
		}
		table {
			border: 1px solid #CCC;
			margin: 10px;
			padding: 3px;
		}
		thead {
			padding-bottom: 3px;
			border-bottom: 1px solid #EEE;
		}
		th,td {
			padding-right: 10px;
			text-align: left;
		}
		h1 {
			margin: 10px 0 0 0;
			padding: 0;
		}
		.pad {
			padding-left: 50px;
		}
	</style>
	</head>
<body><h1><?

/**
 * Connection Details
 * @staticvar string DBHOST	The name of the server on which the MySQL Database is located
 * @staticvar string DBUSER	The name of the user with which to log into the MySQL Database
 * @staticvar string DBPASS	The password for the user of the MySQL Database
 * @staticvar string DBNAME	The name of the MySQL Database on the server
 */
define("DBHOST", "localhost");
define("DBUSER", "root");
define("DBPASS", "");
define("DBNAME", "");

/**
 * Prints out a line to the text file
 * @param string $line	The line of text to output
 * @param integer[optional] $linebreaks	The number of line break to put after the message (Default is 1)
 */
function println($line, $linebreaks=1) {
	echo $line;
	while ($linebreaks > 0) {
		echo "
";
		$linebreaks--;
	}
}

/**
 * Print the Configuration we're using for the MySQL Database
 */
println("Database Host	".DBHOST);
println("Database User	".DBUSER);
println("User Password	".DBPASS);
println("Database Name	".DBNAME, 2);

/**
 * Test the Configuration we're using for the MySQL Database
 */
if ($con = mysql_connect(DBHOST, DBUSER, DBPASS)) {
	println("Successfully connected to MySQL Database: $con");
} else {
	println("Could not connect to MySQL Database");
	die();
}

require_once("mysql_fetch_table.inc.php");

/**
 * Run some queries
 */
$databaseresult = mysql_query("SHOW DATABASES;");
echo "<h1>Databases</h1>".mysql_fetch_table($databaseresult);

while (list($database) = mysql_fetch_array($databaseresult)) {
	echo "<h1>Database: $database</h1><div class=\"pad\">";

	mysql_select_db($database);
	$tableresult = mysql_query("SHOW TABLES;");
	echo "<h2>Tables</h2>".mysql_fetch_table($tableresult);

	while (list($table) = mysql_fetch_array($tableresult)) {
		echo "<h3>Table: $table</h3><div class=\"pad\">";

		$colresult = mysql_query("SHOW FULL COLUMNS FROM $table;");
		echo mysql_fetch_table($colresult);

		mysql_free_result($colresult);
		echo "</div>";
	}

	mysql_free_result($tableresult);
	echo "</div>";
}
mysql_free_result($databaseresult);

/**
 * Close the connection
 */
mysql_close();
println("Disconnected from the MySQL Database");

?>
</body>
</html>

Hope this helps somebody :thumb: