How to generate xml files using php and mysql database

How to generate XML files using php

XML is a popular data interchange and storage format used on the internet. XML stands for eXtensible Markup Language. Custom markups can be created in XML. XML was designed to describe data while HTML was designed for data presentation. In a previous tutorial we learned how to parse XML using php simplexml library, this tutorial explores how to generate XML files using php.

Suppose you are working on an online bookstore application and need to share the book’s information on the website so other people can access this information and show it on their website.

So to share book data with other people you are going to provide an XML file containing books information from MySQL database.

how to generate xml files using php tutorial

In order to generate XML files using php DOMDocument class, we are going to do following steps.

1. Create a database

2. Create a table and insert sample data into it

3. Fetch data from the database and generate XML file.

Create a database dbbookstore and table tbl_books

First, we are going to create database for our bookstore application and a table for books.

SQL for creating database dbbookstore and table tbl_books

CREATE DATABASE dbbookstore;

CREATE TABLE `books` (

  `id` int(11) NOT NULL,

  `title` varchar(500) NOT NULL,

  `author_name` varchar(500) NOT NULL,

  `price` varchar(500) NOT NULL,

  `ISBN` varchar(50) NOT NULL,

  `category` varchar(100) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;



INSERT INTO `books` (`id`, `title`, `author_name`, `price`, `ISBN`, `category`) VALUES

(1, 'C++ By Example', 'John', '500', 'PR-123-A1', 'Programming'),

(2, 'Java Book', 'Jane davis', '450', 'PR-456-A2', 'Programming'),

(3, 'Database Management Systems', 'Mark', '300', 'DB-123-ASD', 
'Database'),

(4, 'Harry Potter and the Order of the Phoenix', 'J.K. Rowling', '650', 'FC-123-456', 'Novel'),

(5, 'Pride and Prejudice', 'Jane Austen', '450', 'FC-456-678', 'Novel'),

(6, 'Learning Web Development ', 'Michael', '300', 'ABC-123-456', 'Web Development'),

(7, 'Professional PHP & MYSQL', 'Programmer Blog', '340', 'PR-123-456', 'Web Development');


ALTER TABLE `books` ADD PRIMARY KEY (`id`);


ALTER TABLE `books`  MODIFY `id` int(11) NOT NULL  AUTO_INCREMENT,AUTO_INCREMENT=8;

mysql database table - generate xml files using php

DOMDocument class to generate XML files using php

PHP DOMDocument class can be used to read or generate XML files. Here we want to generate XML  file from a bookstore database. Generated XML document will contain title, author name, price, ISBN and category of books. An attribute id in book element will be added. In order to do above tasks you have to:

1. Connect to database

2. Select records from books table and add  to an array

3. Create XML file by calling createXMLfile() function

1. Connect to database using mysqli

First you need to connect to database using mysqli method. This method requires host, username, password and database. If connection is successful, a connection object is returned else an error is thrown.

/** create XML file */ 
$mysqli = new mysqli("localhost", "root", "root", "dbbookstore");

/* check connection */
if ($mysqli->connect_errno) {

   echo "Connect failed ".$mysqli->connect_error;

   exit();
}

2. Select records from books table

Next to select records from books table. A query is passed to connection object’s query() method. query() method returns a result set. To fetch data rows from result set fetch_assoc function is used. This function fetches rows from result set as an associative array.

In a while loop data rows are pushed into an array. If there are records in the array then createXMLfile() function is called with books array parameter.

$query = "SELECT id, title, author_name, price, ISBN, category FROM books";

$booksArray = array();

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

       array_push($booksArray, $row);
    }
  
    if(count($booksArray)){

         createXMLfile($booksArray);

     }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

3. Create XML file using createXMLfile() function

Finally to generate XML file using php DOMDocument class a function createXMLfile is created. and array of books data is passed as parameter.

function createXMLfile($booksArray){
  
   $filePath = 'book.xml';

   $dom     = new DOMDocument('1.0', 'utf-8'); 

   $root      = $dom->createElement('books'); 

   for($i=0; $i<count($booksArray); $i++){
     
     $bookId        =  $booksArray[$i]['id'];  

     $bookName      =  $booksArray[$i]['name']; 

     $bookAuthor    =  $booksArray[$i]['author_name']; 

     $bookPrice     =  $booksArray[$i]['price']; 

     $bookISBN      =  $booksArray[$i]['ISBN']; 

     $bookCategory  =  $booksArray[$i]['category'];	

     $book = $dom->createElement('book');

     $book->setAttribute('id', $bookId);

     $name     = $dom->createElement('name', $bookName); 

     $book->appendChild($name); 

     $author   = $dom->createElement('author', $bookAuthor); 

     $book->appendChild($author); 

     $price    = $dom->createElement('price', $bookPrice); 

     $book->appendChild($price); 

     $isbn     = $dom->createElement('ISBN', $bookISBN); 

     $book->appendChild($isbn); 
     
     $category = $dom->createElement('category', $bookCategory); 

     $book->appendChild($category);
 
     $root->appendChild($book);

   }

   $dom->appendChild($root); 

   $dom->save($filePath); 

 } 

Source code to generate XML files using php

<?php

/** create XML file */ 
$mysqli = new mysqli("localhost", "root", "", "dbbookstore");

/* check connection */
if ($mysqli->connect_errno) {

   echo "Connect failed ".$mysqli->connect_error;

   exit();
}

$query = "SELECT id, title, author_name, price, ISBN, category FROM books";

$booksArray = array();

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

       array_push($booksArray, $row);
    }
  
    if(count($booksArray)){

         createXMLfile($booksArray);

     }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

function createXMLfile($booksArray){
  
   $filePath = 'book.xml';

   $dom     = new DOMDocument('1.0', 'utf-8'); 

   $root      = $dom->createElement('books'); 

   for($i=0; $i<count($booksArray); $i++){
     
     $bookId        =  $booksArray[$i]['id'];  

     $bookName      =  $booksArray[$i]['name']; 

     $bookAuthor    =  $booksArray[$i]['author_name']; 

     $bookPrice     =  $booksArray[$i]['price']; 

     $bookISBN      =  $booksArray[$i]['ISBN']; 

     $bookCategory  =  $booksArray[$i]['category'];	

     $book = $dom->createElement('book');

     $book->setAttribute('id', $bookId);

     $name     = $dom->createElement('name', $bookName); 

     $book->appendChild($name); 

     $author   = $dom->createElement('author', $bookAuthor); 

     $book->appendChild($author); 

     $price    = $dom->createElement('price', $bookPrice); 

     $book->appendChild($price); 

     $isbn     = $dom->createElement('ISBN', $bookISBN); 

     $book->appendChild($isbn); 
     
     $category = $dom->createElement('category', $bookCategory); 

     $book->appendChild($category);
 
     $root->appendChild($book);

   }

   $dom->appendChild($root); 

   $dom->save($filePath); 

 } 
Create a file named generatexml.php in your project folder in WAMP or XAMPP copy and paste the code above and run the file in browser. An XML file is generated as shown below.

Generated XML - How to generate xml files using php

Summary

To sum up, in this tutorial we learned how to generate XML files using php from bookstore database. Please leave your valuable feedback and comments.

 

Related Articles:

 

Previous Article: PHP MySqli tutorial

Next Article: PHP PDO Tutorial

 

 

 

  • Pingback: How to generate xml files using php and mysql d...()

  • Pingback: How to parse XML using NodeJS, ExpressJS and xml2js()

  • Rolly Esteban Chin Maldonado

    Friend, how i could send this xml to a web services?

    Thanks

  • Preeti Singh

    can you please tell me ,where will we get this generated xml file.

    • Hi Preeti,

      Thanks for your comment. The XML file is saved in same directory where your PHP file exists.

      As you see path variable is set to book.xml file.

      $filePath = ‘book.xml’;

      And In last you can see we have saved.

      $dom->save($filePath);

      So it is saved in same directory where php file exists.

      Regards,

  • Μπαμπης ειμαι και γουσταρω

    hi thanks for your tutorial first of all.
    i work 1-2 years now to php and mysql but never and is strange i work with Array like for($i=0; $i<count($cityArray); $i++)
    its ok in a few minutes i wrote the code and successfully create and save a xml file .
    i create this xml with UNION ALL from 6 tables were id = city id from city names.

    the results are displayed in random order (ie the first result is and the next time when refresh the page is not in ascending order.
    My question is what should I change in the code to display the results in ascending order, that is to say, ……… is the last city

  • Μπαμπης ειμαι και γουσταρω

    hi thanks for your tutorial first of all.
    i work 1-2 years now to php and mysql but never and is strange i work with Array like for($i=0; $i<count($cityarray); $i++) .

    its ok in a few minutes i wrote the code and
    successfully create and save xml file.

    i create this xml with union all from 6 tables were id="city" id comes from city names and is not AUTO_INCREMENT .

    the results are displayed in random order (ie the first result is and the next time when refresh the page is not in ascending order.
    My question is what should I change in the code to display the results in ascending order, that is to say, ……… is the last city

  • Μπαμπης ειμαι και γουσταρω

    hi thanks for your tutorial first of all. i work 1-2 years now to php and mysql but never and is strange i work with Array.
    its ok in a few minutes i wrote the code and successfully create and save xml file. i create this xml with union all from 6 tables were id=”city” id comes from city names and is not AUTO_INCREMENT . the results are displayed in random order (ie the first result is and the next time when refresh the page is not in ascending order. My question is what should I change in the code to display the results in ascending order, that is to say, ……… is the last city

    • Hi,

      Thanks for your comment.

      In order to give you an exact solution is difficult as I don’t know your code, query and db structure.

      1. One thing, have you done order by city id in ascending order?

      If you do this, then do a print_r(your result here), before creating an array and see what is printed.

      2. On result array you can use some array sorting function like sort() before creating xml array

      3. If you still have issues then please share your code and db structure so can check where the issue is in code.

      Regards,

      • Μπαμπης ειμαι και γουσταρω

        From the many hours I write code, my eyes hacked hahaha (I’m building a weather page for all of europe) and
        I did not notice a simple mistake.
        I used ORDER BY time ASC while I thought I had changed it
        ORDER BY id ASC.
        simple mistakes unfortunately many times we do not see them.
        thank you for your interest, continue the very good job

        • Hi,
          Yes some time it happens, sometime minor issue takes time. Best of luck with the project.

          ThankYou

  • Ormila Carter Sookdeo

    Hello i tried the code and it works for me but when i open the xml file all the code comes out horizontally in one straight line..can u help???

    • Hi,
      Thanks for your comment

      How are you opening the file, Means

      1. First you can open in Internet explorer or google chrome, I should be formatted.
      2. If you are opening in editor then try Wrap the content option.

      I hope this helps, If still have an issue let us know.

      ThankYou

      • Ormila Carter Sookdeo

        Ok so my database is in mysql workbench and i have xampp to run the servers and im using notepad++ as my editor so i run in my browser by using the following “localhost/filename.php” and it creates the file in the file path…..but it comes out horizontally….

        • Notepad++ will not do the formatting for you, Try to open in some other editor or in chrome / Internet Explorer.