mysql> select * from bands; +----+--------------+------------+-------------+-------------+ | id | name         | birth_year | birth_place | genre       | +----+--------------+------------+-------------+-------------+ |  1 | The Blabbers |       1998 | London      | Rock'n'Roll | |  2 | Only Stupids |       1997 | New York    | Hip Hop     | +----+--------------+------------+-------------+-------------+  mysql> select * from albums; +----+---------+------------------+------+-----------------+ | id | bandsID | title            | year | comment         | +----+---------+------------------+------+-----------------+ |  1 |       1 | BlaBla           | 1998 | Their first one | |  2 |       1 | More Talks       | 2000 | The second one  | |  3 |       2 | All your base... | 1999 | The Classic     | +----+---------+------------------+------+-----------------+

The typical using

Let's start with an example using the default options. The new instance is bind to an DSN, so you have only to provide an SQL query. The instance fetches the result automatically; in $xmlstring you found the XML representation of the result set.

Example 42-1. The simplest example

<?php require_once "XML/sql2xml.php"; $sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest"); $xmlstring = $sql2xmlclass->getxml("select * from bands"); ?>

The content of $xmlstring based on the DB tables above is:

<?xml version="1.0"?>     <root>         <result>             <row>                 <id>1</id>                 <name>The Blabbers</name>                 <birth_year>1998</birth_year>                 <birth_place>London</birth_place>                 <genre>Rock'n'Roll</genre>             </row>             <row>                 <id>2</id>                 <name>Only Stupids</name>                 <birth_year>1997</birth_year>                 <birth_place>New York</birth_place>                 <genre>Hip Hop</genre>             </row>         </result>     </root>

Transformations based on Join queries

If your query result base on joined tables, a nested XML data structure can represent how the DBMS joins the tables. To enable or to disable this behavoir use setOptions() with the option key 'nested'. The default value is TRUE - the nesting is enabled.

Example 42-2. Nested result set

<?php $sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest"); $xmlstring = $sql2xmlclass->getxml("select * from bands left join albums on bands.id = bandsID"); ?>

The generated XML output in $xmlstring:

<?xml version="1.0"?>     <root>         <result>             <row>                 <id>1</id>                 <name>The Blabbers</name>                 <birth_year>1998</birth_year>                 <birth_place>London</birth_place>                 <genre>Rock'n'Roll</genre>                 <row>                     <id>1</id>                     <bandsID>1</bandsID>                     <title>BlaBla</title>                     <year>1998</year>                     <comment>Their first one</comment>                 </row>                 <row>                     <id>2</id>                     <bandsID>1</bandsID>                     <title>More Talks</title>                     <year>2000</year>                     <comment>The second one</comment>                 </row>             </row>             <row>                 <id>2</id>                 <name>Only Stupids</name>                 <birth_year>1997</birth_year>                 <birth_place>New York</birth_place>                 <genre>Hip Hop</genre>                 <row>                     <id>3</id>                     <bandsID>2</bandsID>                     <title>All your base...</title>                     <year>1999</year>                     <comment>The Classic</comment>                 </row>             </row>         </result>     </root>

If you disable the nesting, the XML structure of rows is flat.

Example 42-3. Unnested result sets

<?php $sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest"); $options      = array('nested' => false); $sql2xmlclass->setOptions($options); $xmlstring    = $sql2xmlclass->getxml("select * from bands left join albums on bands.id = bandsID"); ?>

XML output:

$xmlstring =>  <?xml version="1.0"?>     <root>         <result>             <row>                 <id>1</id>                 <name>The Blabbers</name>                 <birth_year>1998</birth_year>                 <birth_place>London</birth_place>                 <genre>Rock'n'Roll</genre>                 <id>1</id>                 <bandsID>1</bandsID>                 <title>BlaBla</title>                 <year>1998</year>                 <comment>Their first one</comment>             </row>             <row>                 <id>1</id>                 <name>The Blabbers</name>                 <birth_year>1998</birth_year>                 <birth_place>London</birth_place>                 <genre>Rock'n'Roll</genre>                 <id>2</id>                 <bandsID>1</bandsID>                 <title>More Talks</title>                 <year>2000</year>                 <comment>The second one</comment>             </row>             <row>                 <id>2</id>                 <name>Only Stupids</name>                 <birth_year>1997</birth_year>                 <birth_place>New York</birth_place>                 <genre>Hip Hop</genre>                 <id>3</id>                 <bandsID>2</bandsID>                 <title>All your base...</title>                 <year>1999</year>                 <comment>The Classic</comment>             </row>         </result>     </root>

Prev	Home	Next
XML_sql2xml	Up	Introduction

Who's Online
Guest Users: 9

Free Sample Chapters
	Deliver First Class Web Sites: 101 Essential Checklists Want to learn how to make your web sites usable and accessible? Want to ensure that your sites meet current best practice, without spending hours trawling through incomprehensible specifications and recommendations from dozens of different books, research papers, and web sites? Want to make sure that the sites you build are "right the first time," requiring no costly redevelopments?
More Sample Chapters

PHP General