Introduction -- Simple transformations Example database
In this tutorial, the examples refers to this
database tables:
mysql> select * from bands;
+----+--------------+------------+-------------+-------------+
| idname birth_yearbirth_placegenre |
+----+--------------+------------+-------------+-------------+
| 1The Blabbers 1998London Rock'n'Roll |
| 2Only Stupids 1997New York Hip Hop |
+----+--------------+------------+-------------+-------------+
mysql> select * from albums;
+----+---------+------------------+------+-----------------+
| idbandsIDtitle yearcomment |
+----+---------+------------------+------+-----------------+
| 1 1BlaBla 1998Their first one |
| 2 1More Talks 2000The second one |
| 3 2All your base...1999The 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.
Пример 43-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.
Пример 43-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.
Пример 43-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> |
|
|
