Advanced Search

Export Database Schema as XML

/ Published in: PHP
Save to your folder(s)
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.

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. <?php
  2. // database constants
  3. // make sure the information is correct
  4. define("DB_SERVER", "localhost");
  5. define("DB_USER", "root");
  6. define("DB_PASS", "password");
  7. define("DB_NAME", "tutorials");
  8.  
  9. // connection to the database
  10. $dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
  11.    or die("Unable to connect to MySQL");
  12.  
  13. // select a database to work with
  14. $selected = mysql_select_db(DB_NAME, $dbhandle)
  15.    or die("Could not select examples");
  16.  
  17. // return all available tables 
  18. $result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle );
  19.  
  20. $tables = array();
  21. while ($row = mysql_fetch_row($result_tbl)) {
  22.    $tables[] = $row[0];
  23. }
  24.  
  25. $output = "<?xml version=\"1.0\" ?>\n";
  26. $output .= "<schema>";
  27.  
  28. // iterate over each table and return the fields for each table
  29. foreach ( $tables as $table ) {
  30.    $output .= "<table name=\"$table\">";
  31.    $result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle );
  32.  
  33.    while( $row1 = mysql_fetch_row($result_fld) ) {
  34.       $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
  35.       $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
  36.    }
  37.  
  38.    $output .= "</table>";
  39. }
  40.  
  41. $output .= "</schema>"; 
  42.  
  43. // tell the browser what kind of file is come in
  44. header("Content-type: text/xml");
  45. // print out XML that describes the schema
  46. echo $output;
  47.  
  48. // close the connection
  49. mysql_close($dbhandle);
  50. ?>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.