Get MySQL database layout in XML format

Get MySQL database layout in XML format

In this tutorial I will show you how to get your MySQL database description / layout in an XML format. If you are an absolute beginner in XML than it can be useful to read first an XML introduction tutorial.

Step 1.
To create a description in XML format first we need to connect to the database. The connection parameters are hard coded in this tutorial but of course you can write a small html form to get the values dynamically from the user.

So the connection code is quite simple:

<?php

$host = 'localhost';
$user = 'testuser';
$pass = 'mypass';
$db   = 'demo';
    
// Connecto to the MySQL server
$link = mysql_connect($host,$user,$pass);
if ($link){
      // Select the database
    mysql_selectdb($db,$link);
?>

Step 2.
As we have a connection first we write this information in our XML document. In this case we just output the XML content on the display and you can copy the content to a file if you want. To compose the XML header is not so complicated but you have to take care using the PHP built in function htmlspecialchars() to convert special characters to HTML entities. Without this echo will not display the XML code correctly. To make the output more readable I just add a simple HTML br tag at the end (outside of the htmlspecialchars()).

The code is this:

<?php
    // Write xml header information
    echo htmlspecialchars(
      '<DBSchema db="'.$db.'" host="'.$host.'" user="'.$user.'" pass="'.$pass.'">');
    echo "<br/>";
?>

Step 3.
Now we have to get all tables inside this database. We can do this by using the SQL command SHOW TABLES. The result of this command will give you the complete list of tables.
Now we have to go through all of the tables and create their description. Let's create a while loop where each iteration has a new table name.

The code looks like this:

<?php
    // Get all tables
    $result = mysql_query('SHOW TABLES');
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        // Get table name
        $table = $row[0]  ;
?>

Ok, now we have the actual table name but how we can get the table description with field names, types and so on. There is again a nice SQL command called DESCRIBE we can use to do this. The correct syntax is DESCRIBE tablename. As we have the tablename it is quite easy. Before we process table description we need to output the actual table header to the XML output. It is similar to main header output. Because of readability I added some HTML spaces to make some text indent in the output.

The code for this is:

<?php
        // Get table info        
        $struct = mysql_query('DESCRIBE '.$table);

        // Write table info       
        echo "&nbsp;&nbsp;&nbsp;"
             .htmlspecialchars('<Table name="'.$table.'">')."<br/>";
?>

Step 4.
In this step we make a new while loop to go through all of the fields inside the table. We process the resultset and build an XML output for each field. I have separated all field property in a new line to make the code more readable.

The fields output is this:

<?php

        // Get table description       
        while ($row2 = mysql_fetch_array($struct, MYSQL_NUM)) {
            $autoi = (strstr($row2[5],'auto_increment')) ? 'true' : 'false';
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
            echo htmlspecialchars('<Field name="'.$row2[0].'" ');
            echo htmlspecialchars('type="'.$row2[1].'" ');
            echo htmlspecialchars('null="'.$row2[2].'" ');
            echo htmlspecialchars('key="'.$row2[3].'" ');
            echo htmlspecialchars('default="'.$row2[4].'" ');
            echo htmlspecialchars('autoincrement="'.$autoi.'"/>')."<br/>";
        }
?>

Step 5.
As final step we need to close all relevant tags and we are ready. Just set connection parameters and run the script and you get the complete description of your database in XML format. You can extend this script by adding an outer loop and you can get all database description on the actual MySQL server.

The complete code looks like this:

<?php

$host = 'localhost';
$user = 'testuser';
$pass = 'mypass';
$db   = 'demo';
    
// Connecto to the MySQL server
$link = mysql_connect($host,$user,$pass);
if ($link){
      // Select the database
    mysql_selectdb($db,$link);
        
    // Write xml header information
    echo htmlspecialchars(
      '<DBSchema db="'.$db.'" host="'.$host.'" user="'.$user.'" pass="'.$pass.'">');
    echo "<br/>";
           
    // Get all tables
    $result = mysql_query('SHOW TABLES');
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        // Get table name
        $table = $row[0]  ;
        
        // Get table info        
        $struct = mysql_query('DESCRIBE '.$table);

        // Write table info       
        echo "&nbsp;&nbsp;&nbsp;"
             .htmlspecialchars('<Table name="'.$table.'">')."<br/>";

        // Get table description       
        while ($row2 = mysql_fetch_array($struct, MYSQL_NUM)) {
            $autoi = (strstr($row2[5],'auto_increment')) ? 'true' : 'false';
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
            echo htmlspecialchars('<Field name="'.$row2[0].'" ');
            echo htmlspecialchars('type="'.$row2[1].'" ');
            echo htmlspecialchars('null="'.$row2[2].'" ');
            echo htmlspecialchars('key="'.$row2[3].'" ');
            echo htmlspecialchars('default="'.$row2[4].'" ');
            echo htmlspecialchars('autoincrement="'.$autoi.'"/>')."<br/>";
        }

        // Write table section close element       
        echo "&nbsp;&nbsp;&nbsp;"
            .htmlspecialchars('</Table>')."<br/>";
                
    }
    
    // Write the final close element
    echo htmlspecialchars('</DBSchema>')."<br/>";
}
  
?>

Download
You can also download a complete DB to XML code from this site.