Introduction

PEAR Manual
Prev		Next

Example database

In this tutorial, the examples refers to this database tables:
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 68-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 68-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 68-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>