PHP: Export Database Schema as XML

April 17, 2007 at 8:45 am 1 comment

Sometimes it can be useful to have a dump of the current database schema. The script below reads the schema from a MySQL database and outputs XML that describes the schema.

At first we connect to a MySQL database and use the SHOW TABLES command to return all the tables in the database. Next, we iterate over each table and return the fields for each table using the SHOW FIELDS command. Finally, we put all of the returned information into XML.

Have a look at the code:

<?php
// database constants
// make sure the information is correct

define(“DB_SERVER”, “localhost”);
define(“DB_USER”, “root”);
define(“DB_PASS”, “password”);
define(“DB_NAME”, “tutorials”);

// connection to the database
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
or die(“Unable to connect to MySQL”);

// select a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
or die(“Could not select examples”);

// return all 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>”;

// iterate over each table and return the fields for each table
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>”;

// tell the browser what kind of file is come in
header(“Content-type: text/xml”);
// print out XML that describes the schema
echo $output;

// close the connection

mysql_close($dbhandle);
?>

You should note that this code is specific to MySQL database. The commands such as SHOW TABLES and SHOW FIELDS are also available for other databases but are specified slightly differently.

Entry filed under: PHP. Tags: .

Inserting Data Into Your Table 5 useful PHP functions for MySQL data fetching

1 Comment Add your own

  • 1. Ravi  |  July 11, 2007 at 6:05 pm

    Cant this be done in Oracle? I have an urgent need for the same operation to be performed in Oracle, but cant figure out a way… Need some advice🙂

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Flickr Photos

The firsts lights

Lago di Braies

Survivor Mode

More Photos

Blog Stats

  • 705,777 hits

%d bloggers like this: