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 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 books information on 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 database and generate XML file.

Create a database dbbookstore and table tbl_books

First we are going to create database for our book store 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.

 

About Jason William