Exporting a MySQL Database Schema as XML

Dumping a database schema is a an often required quick task. This script will read the schema from a MySQL database, and output it XML to describe it.


<?php
// Define a few constants
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "password");
define("DB_NAME", "example");

// Lets connect to the db
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
or die("Unable to connect to MySQL");

// Choose a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
or die("Could not select examples");
// Return all of the available tables
$result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle );

$tables = array();
while ($row = mysql_fetch_row($result_tbl)) {
$tables[] = $row[0];
}

$output = "<?xml version=\"1.0\" ?>\n";
$output .= "<schema>";

// Now iterate through each table and return the fields
foreach ( $tables as $table ) {
$output .= "<table name=\"$table\">";
$result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle );

while( $row1 = mysql_fetch_row($result_fld) ) {
$output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
$output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
}

$output .= "</table>";
}

$output .= "</schema>";

// Notify the browser the type of file being dealt with
header("Content-type: text/xml");
// Display the XML to describe the schema
echo $output;

// Dont forget to close the connection
mysql_close($dbhandle);
?>

Remember, this is an example, and real world code should escape characters, follow best security practices etc. Proof of concept code here, – not a snippet to put live.

Leave a Reply

Your email address will not be published. Required fields are marked *