Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Access Functions
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

PHP: Export Database Schema as XML

Print

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.

Back to top

Related Articles

 PHP: Constants
 PHP: Looping Statements
 How to connect to MySQL database using PHP




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.