Generate XML File of MySQL Data using PHP

Prerequisite: Basic Understanding HTML, PHP and MySQL.

XML is an Extensible Markup Language similar to HTML. It is used to structure, store and transport data from one system to another. Users can define their own tags in XML which makes it flexible. Any sort of data stored in databases can be easily converted to a XML format. XML content is encoded as UTF-8 in .xml files

XML should not be considered as an alternative or replacement to HTML But the basic difference between HTML and XML is: XML is used for transporting data between database and some applications. However, HTML is used to define the way of displaying data to a webpage. HTML is not very strict for its rules. However, XML is very strict as it kills the process in case any of its rule is broken.

In this project:

• We will fetch all the active user records from the MySQL Database using PHP.
• Resulted user records will be stored in an array.
• The array will be processed further to transfer the data to a XML File.
• File created will be stored inside the files folder which resides in the same root directory. i.e. http://localhost/generate_xml.
• We have used two methods of generating XML, which are mentioned as follows:
• SimpleParser
• DOM (Document Object Model)
• The XML Files created, can be accessed anytime as the data is permanently stored on those files. 

index.php ⬇

<?php
include_once 'includes/conn.php';
$sql = "SELECT a.user_id, a.name, a.email, b.department, b.salary FROM users a
                LEFT JOIN user_details b ON a.user_id  = b.user_id
			WHERE a.status = 1";
$result = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($result);
if($num_rows > 0){
    $userArr = array();
    while($row = mysqli_fetch_assoc($result)){
        $arr['user_id'] = $row['user_id'];
        $arr['name'] = $row['name'];
        $arr['email'] = $row['email'];
        $arr['department'] = $row['department'];
        $arr['salary'] = $row['salary'];
        $userArr[] = $arr;
    }
    $xml = new SimpleXMLElement('<user></user>');
    foreach($userArr as $outerkey => $innerArr){        
        $xml->addChild('details');
        $xml->details[$outerkey]->addAttribute('id', $outerkey);
        foreach($innerArr as $key => $value){
            $xml->details[$outerkey]->addChild($key, $value);
        }         
    }   
    $xml->asXML('files/user_data.xml');
}
?>

Explanation of Index.php

• PHP in-built method include_once() is used to include the connection file in our program which is used to create connection with mydb database.

  include_once 'includes/conn.php';

• Connection is created with “mydb” database and in case of any error, Error message is displayed on the screen and exit() method is called. Conn.php file has the following set of code:

$con = new mysqli("localhost","root","","mydb");
if($con->connect_errno) {
  echo "Failed to connect to MySQL: ". $con->connect_error;
  exit();
}

• We have created “users” and “user_details” table in the database and also we have entered some dummy records to fetch the data to create a XML File. You can create the tables as per your own requirement. Our tables look like as:

users Table:

 Where,

user_id is the primary key of users table.

user_details Table:

Where,

Id is the primary key of user_details table and user_id acts as foreign key for this table and refers to primary key of users table. 

LEFT JOIN is used to fetch the name, email, department and salary of all the users existed in the records.

• Records are fetched using mysqli_query, If the user records exists then the results are stored in an array i.e. $userArr.

while($row = mysqli_fetch_assoc($result)){
   $arr['user_id'] = $row['user_id'];
   $arr['name'] = $row['name'];
   $arr['email'] = $row['email'];
   $arr['department'] = $row['department'];
   $arr['salary'] = $row['salary'];
   $userArr[] = $arr;
}

• We have used a tree-based parser, SimpleXML method to generate the XML File. It is the easiest method to create XML file. With the help of this method we have transformed the MySQL data into collection of Arrays which can be iterated for processing. Like:

$xml = new SimpleXMLElement('<user></user>');
foreach($userArr as $outerkey => $innerArr){        
    $xml->addChild('details');
    $xml->details[$outerkey]->addAttribute('id', $outerkey);
    foreach($innerArr as $key => $value){
       $xml->details[$outerkey]->addChild($key, $value);
    }         
}   

For an instance,

<user>			// main element
	<details id=”1”>	// added child with attribute “Id”
		<value1>          // added child to parent details
		<value2>
	</details>
</user>

• asxml() method is called alongwith the name and path of XML file to be created. Code is:

$xml->asXML('files/user_data.xml');

Here, we have saved our file inside the files folder named as user_data.xml.

• When you run your index.php file in web browser, an XML File with following output will be generated:

user_data.xml
      <user>
<details id="0">
<user_id>1</user_id>
<name>smith</name>
<email>[email protected]</email>
<department>IT</department>
<salary>1000000</salary>
</details>
<details id="1">
<user_id>2</user_id>
<name>john</name>
<email>[email protected]</email>
<department>Marketing</department>
<salary>150000</salary>
</details>
      </user>

In a similar manner, you can fetch the records from MySQL Database and create any kind of arrays and objects in XML File.

There are also two other methods to generate XML file dynamically using PHP.

• DOM (Document Object Model) 
• XML Reader/Writer

DOM Method: Similar data can be converted using DOM Method as follows:

<?php
include_once 'includes/conn.php';
$sql = "SELECT a.user_id, a.name, a.email, b.department, b.salary FROM users a
                LEFT JOIN user_details b ON a.user_id  = b.user_id";
$result = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($result);
 
if($num_rows > 0){
    $userArr = array();
    while($row = mysqli_fetch_assoc($result)){
        $arr['user_id'] = $row['user_id'];
        $arr['name'] = $row['name'];
        $arr['email'] = $row['email'];
        $arr['department'] = $row['department'];
        $arr['salary'] = $row['salary'];
        $userArr[] = $arr;
    }


// DOM (Document Object Model) Method for Generating XML


    $xml = new DOMDocument('1.0','UTF-8');
    $xml->formatOutput = true;
    $root = $xml->createElement('user');
    $xml->appendChild($root);
    
    foreach($userArr as $outerkey => $innerArr){     
        $details = $xml->createElement('details');
        $root->appendChild($details);
        $details->setAttribute('id', $outerkey);
        
        foreach($innerArr as $key => $value){
            $details->appendChild($xml->createElement($key, $value) );
        }         
    }   
    $xml->saveXML();
    $xml->save('files/user_data_dom.xml');


// end DOM
}
?>

Document Object Model XML method is very fast for the smaller documents. Anything can be traversed in the tree. Interface is very simple to understand. We can also manipulate the file once created using this method. Output generated for the above method will be:

user_data_dom.xml

<user>
<details id="0">
<user_id>1</user_id>
<name>smith</name>
<email>[email protected]</email>
<department>IT</department>
<salary>1000000</salary>
</details>
<details id="1">
<user_id>2</user_id>
<name>john</name>
<email>[email protected]</email>
<department>Marketing</department>
<salary>150000</salary>
</details>
</user>

Summary

In this project, we have learned how to generate a simple XML document with PHP while fetching the data from MySQL Database. We have created two tables users and user_details in MySQL Database with some dummy records and the records are fetched and are converted to a XML File dynamically using SimpleParser and Document Object Model (DOM) XML Method of Parsing.