In this tutorial we will generate an XML file from the data stored in MySQL database. As an example we will use a database table storing UK National Lottery Lotto draw results and we will generate an XML file that contains last 2 draw’s results.

This is pretty easy to do and will give you an idea how to approach MySQL to XML conversion.

So first, our database table:

Don’t worry about date_added and validated fields. We’re not going to need them.

The screenshot above is a result of the following query:

SELECT * FROM results_lotto ORDER BY draw_id DESC LIMIT 2

Which will be right if we want to generate XML file that contains results of last 2 draws stored in results_lotto table.

Now, let’s say we want to use PHP to generate XML file that looks like this:

<?xml version="1.0"?>
<xml>
   <draw>
      <number>1547</number>
      <date>2010-10-20</date>
      <ball1>14</ball1>
      <ball2>30</ball2>
      <ball3>31</ball3>
      <ball4>36</ball4>
      <ball5>44</ball5>
      <ball6>47</ball6>
      <bonus_ball>26</bonus_ball>
   </draw>
   <draw>
      <number>1546</number>
      <date>2010-10-16</date>
      <ball1>6</ball1>
      <ball2>30</ball2>
      <ball3>34</ball3>
      <ball4>38</ball4>
      <ball5>45</ball5>
      <ball6>48</ball6>
      <bonus_ball>32</bonus_ball>
   </draw>
</xml>

As you can see the XML file contains all the useful information from our table.

Let’s say we want PHP script to generate this XML file and store it on the server.

First we will have to initiate database connection:

$db_host = "database_host";
$db_user = "database_user";
$db_pass = "database_pass";
$db_name = "database_name";

$db = mysql_connect($db_host, $db_user, $db_pass);
if (!$db) {
 die('Could not connect: ' . mysql_error());
}

mysql_select_db($db_name,$db);

… query the database:

$result = mysql_query("SELECT * FROM results_lotto
                                       ORDER BY draw_id DESC LIMIT 2", $db);

initialize variable which will store out xml code:

$xml = new SimpleXMLElement('<xml/>');

and parse results into XML format:

while($row = mysql_fetch_assoc($result)) {
 $draw = $xml->addChild('draw');
 $draw->addChild('number',$row['draw_id']);
 $draw->addChild('date',$row['date']);
 $draw->addChild('ball1',$row['ball1']);
 $draw->addChild('ball2',$row['ball2']);
 $draw->addChild('ball3',$row['ball3']);
 $draw->addChild('ball4',$row['ball4']);
 $draw->addChild('ball5',$row['ball5']);
 $draw->addChild('ball6',$row['ball6']);
 $draw->addChild('bonus_ball',$row['bonus_ball']);
}

Finally, let’s write our $xml variable to a file on the server:

$fp = fopen("xml/lotto.xml","wb");
fwrite($fp,$xml->asXML());
fclose($fp);

Above will create lotto.xml file in xml directory on the server. Xml directory would need appropriate permissions for web server to write in it.

mysql_close($db);
$fp = fopen("xml/lotto.xml","wb");
fwrite($fp,$xml->asXML());
fclose($fp);

To make it tidy let’s close mysql connection too:

mysql_close($db);

Additional information on SimpleXMLElement can be found at:

http://www.php.net/manual/en/class.simplexmlelement.php