From MySQL to XML with PHP

PHP XML generator can be written in less then 100 lines. In this example, data from the MySQL database are converted to the XML. PHP function sql2xml has optional second parameter to describe the XML hierarchy, while first parameter is SQL query. Second parameter is string in form of integers separated by commas. For example, to describe XML structure in three levels, second parameter can look like “2,3”. Each integer defines number of columns in SQL query. The last level is not needed to specify.

Here is example of how to use the sql2xml PHP function.

PHP function will transform SQL to the XML and here is the result. Column names from the SQL are used for XML tags. This example has a hierarchical structure in two levels because only first level is defined. Two columns (alb_id and alb_name) are the first level, while the other columns from the SQL are in the second level.

  1 Nevermind 1 Breed  2 Come As You Are   2 Band of Gypsys 1 Who Knows  2 Machine Gun    

PHP function will make connection to the MySQL database and transform SQL to the XML. Please, don’t forget to set MySQL username and password in mysql_pconnect line at the beginning of sql2xml() function.

 else < // set hierarchy levels and number of levels $hierarchy = explode(',', $structure); $deep = count($hierarchy); // set flags for opened tags for ($i = 0; $i // set initial row for ($i = 0; $i < $ncols; $i++) < $rowPrev[$i] = microtime(); >> // loop through result set while ($row = mysql_fetch_row($result)) < // loop through hierarchy levels (data set columns) for ($level = 0, $pos = 0; $level < $deep; $level++) < // prepare row segments to compare for ($i = $pos; $i < $pos+$hierarchy[$level]; $i++) < $row_current .= trim($row[$i]); $row_previous .= trim($rowPrev[$i]); >// test row segments between row_current and row_previous // it should be "!==" and not "! \n"; > $tagOpened[$i] = false; > // reset the rest of rowPrev for ($i = $pos; $i < $ncols; $i++) < $rowPrev[$i] = microtime(); >// set flag to open $tagOpened[$level] = true; print "\n"; // loop through hierarchy levels for ($i = $pos; $i < $pos + $hierarchy[$level]; $i++) < $name = strtoupper(mysql_field_name($result, $i)); print ""; print trim(htmlspecialchars($row[$i],$i)); print "\n"; > > // increment row position $pos += $hierarchy[$level]; // reset row segments (part of columns) $row_current = $row_previous = ''; > // print rest print "\n"; for ($i = $pos; $i < $ncols; $i++) < $name = strtoupper(mysql_field_name($result, $i)); print ""; print trim(htmlspecialchars($row[$i],$i)); print "\n"; > print "\n"; // remember previous row $rowPrev = $row; > // close opened tags for ($level = $deep; $level >= 0; $level--) < if ($tagOpened[$level]) < print "\n"; > > > ?>

Here are create table statements for tables album and song used in this example.

create table album ( alb_id int(11) not null auto_increment, alb_name varchar(32) not null, PRIMARY KEY (alb_id) ); insert into album values (1,'Nevermind'), (2,'Band of Gypsys'); create table song ( sng_id int(11) not null auto_increment, alb_id int(11) not null, sng_number int(11) not null, sng_name varchar(64) not null, PRIMARY KEY (sng_id) ); insert into song values (1, 1, 1,'Breed'), (2, 1, 2,'Come As You Are'), (3, 1, 3,'Drain You'), (4, 1, 4,'Endless, Nameless'), (5, 1, 5,'In Bloom Lyrics'), (6, 1, 6,'Lithium'), (7, 1, 7,'Lounge Act'), (8, 1, 8,'On A Plain'), (9, 1, 9,'Polly'), (10,1,10,'Smells Like Teen Spirit'), (11,1,11,'Something In The Way'), (12,1,12,'Stay Away'), (13,1,13,'Territorial Pissings'), (14,2, 1,'Who Knows'), (15,2, 2,'Machine Gun'), (16,2, 3,'Changes'), (17,2, 4,'Power to Love'), (18,2, 5,'Message to Love'), (19,2, 6,'We Gotta Live Together');

In my next post From MySQL to HTML with PHP and XML, you can find how to transform XML from this example to the HTML. Sounds a bit complicated, but if the Web architecture is set in this way, you will have separated presentation layer with cleaner and simpler PHP code. You can also download redips1.tar.gz (3KB) package. It contains files and examples from this and next post.

Источник

Tutorial: Create XML file from MySQL Database using PHP/MySQLi with Source Code

About How to Create XML file from MySQL Database using PHP MySQL

This is a tutorial on How to Create XML file from MySQL Database using PHP/MySQLi. I’ve used bootstrap to improve the design of the presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link .

Creating a Database

Next, we create our MySQL database where we fetch data into an XML file.

I’ve included a .sql file in the downloadable of this tutorial which is a MySQL database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named database .

How to Create XML file from MySQL Database using PHP

Creating a Link to Create XML

Next, we create the link to create our XML by creating a new file and name it as index.php . Also, I’ve included showing the data in our table in this file.

       
Create an XML file from MySQL Database
 
 
 
 
Create XML 
 
 ?>
 
 query($sql); while($row = $query->fetch_array()) < ?>     ?>
 
UserID
 
Firstname
 
Lastname
 
Address
 
 
 
 
 
 
  

Creating a Create XML Script

Lastly, we create our script that creates an XML file from our MySQL Database.

Create a new file named create_xml.php and paste the below codes.

query($sql); while($row = $query->fetch_assoc()) < $data[] = $row; >//converting our array into xml file //create the xml document $xml = new DOMDocument(); $root = $xml->createElement('users'); foreach($data as $user)< $userRow = $root->appendChild($xml->createElement('user')); //populate user info foreach($user as $key => $val)< $userRow->appendChild($xml->createElement($key, $val)); > > $xml->appendChild($root); header("Content-Type: text/plain"); //make the output pretty $xml->formatOutput = true; //save xml file $xml->save('files/members.xml'); $_SESSION['message'] = 'XML file created. Check your files folder'; header('location: index.php'); ?>

P.S. Don’t forget to create a folder named files . That is where the created XML files are saved.

That ends this tutorial. Happy Coding!

Источник

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.

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

2. Create a table and insert sample data into it

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

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;

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:

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; $icreateElement('book'); $book->setAttribute('id', $bookId); $name = $dom->createElement('title', $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

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; $icreateElement('book'); $book->setAttribute('id', $bookId); $name = $dom->createElement('title', $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.

Download source code from GitHub

Source code for this tutorial can be found at GitHub. Please clone or download from this repository.

Best PHP MYSQL Course

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:

Источник