Mighty Webdeveloper

How to  convert any MySQL table and data to a well-formed XML document using PHP. Building a  generic solution to a common problem.

In this tutorial we will create a standardized script that will convert a MySQL table of any shape or size into a valid XML document. This can be useful for any situation where you have to transfer data from a MySQL database into an XML based system. In some cases the "mysqldump –xml" might be sufficient. If you require output that’s different from the mysqldump format, or if you don’t have access to the mysqldump tool you will need to code your own solution. Creating a single-use script to handle specific data is of course quite easy, but would require custom coding for each table that needs to be converted to XML. I’m going to show you how (using a handy PHP function) you can build a more generalized solution. This mechanism will allow you to convert a MySQL table to XML, but also to a PHP array, CSV file (even though MySQL already supports CSV natively) or any other required format.

Let’s start with an example table and some data:

Table name: fruit

+--------------+----------------+------------------+--------------+
| fruit_id     | fruit_name     | fruit_colour     | price_per_kg |
+--------------+----------------+------------------+--------------+
| 1            | Banana         | yellow           | 2,99         |
| 2            | Orange         | orange           | 2,45         |
| 3            | Strawberries   | red              | 4,99         |
+--------------+----------------+------------------+--------------+

After running our PHP script the resulting XML will look like this:

<?xml version="1.0" encoding="UTF-8"?>
<fruits>
   <fruit>
      <fruit_id>1</fruit_id>
      <fruit_name>Banana</fruit_name>
      <fruit_colour>yellow</fruit_colour>
      <price_per_kg>2,99</price_per_kg>
   </fruit>
   <fruit>
      <fruit_id>2</fruit_id>
      <fruit_name>Orange</fruit_name>
      <fruit_colour>orange</fruit_colour>
      <price_per_kg>2,45</price_per_kg>
   </fruit>
   <fruit>
      <fruit_id>3</fruit_id>
      <fruit_name>Strawberry</fruit_name>
      <fruit_colour>red</fruit_colour>
      <price_per_kg>4,99</price_per_kg>
   </fruit>
</fruits>

Pretty neat huh? So let’s get down to business and start by setting up our database connection:

<?php
//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "root";
$config['mysql_pass'] = "grape01";
$config['db_name']    = "fruit_store";
$config['table_name'] = "fruit";

//connect to host
mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
//select database
@mysql_select_db($config['db_name']) or die( "Unable to select database");

We start building the actual XML by creating the XML header and the necessary root element (which we derive from the table name, add “s” for plural!):

$xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$root_element = $config['table_name']."s"; //fruits
$xml         .= "<$root_element>";

And now for the actual fun stuff! We select the items from the table and use the mysql_fetch_assoc() function to return the query result as an associative array.

//select all items in table
$sql = "SELECT * FROM ".$config['table_name'];

$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

if(mysql_num_rows($result)>0)
{
   while($result_array = mysql_fetch_assoc($result))
   {
      $xml .= "<".$config['table_name'].">";

      //loop through each key,value pair in row
      foreach($result_array as $key => $value)
      {
         //$key holds the table column name
         $xml .= "<$key>";

         //embed the SQL data in a CDATA element to avoid XML entity issues
         $xml .= "<![CDATA[$value]]>"; 

         //and close the element
         $xml .= "</$key>";
      }

      $xml.="</".$config['table_name'].">";
   }
}

That’s it! We have created a list of XML elements representing the MySQL data. Now all we have to do is properly close the XML and output the data to (in this case) the browser:

//close the root element
$xml .= "</$root_element>";

//send the xml header to the browser
header ("Content-Type:text/xml"); 

//output the XML data
echo $xml;
?>

The script can be easily modified to allow for different types of XML (think element names vs attributes). If the script is accessed through a URL we can make the script even more flexible by passing the table or column name(s) as a parameter. Always make sure that the SQL data doesn’t hold any (unicode) characters that are not allowed in XML! I will post a few more conversion examples in another post.

Please let me know if you have any questions or issues with the code using the comment form below.