Home Adauga resursa Intra in cont



Home Comunitate Forum Blog Documentatie Gazduire Promovare/SEO Locuri de munca Proiecte Director Galerie CSS

Introduction

PEAR Manual
Prev		Next

Introduction

Introduction -- Simple transformations

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 64-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 64-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 64-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

Ultimele discutii in forum

Ultimele articole

Membri recenti

Smoky89	OFFETSGOF
user_123	obresitto
BrainSpotting	dracutzu
pitt_sm	radusuteu
andrei4k	tonymotola

Top membri

Pirahna la birou	carco Bucuresti
Birkoff Bucuresti	mihaitha Sibiu
Mascka Braila	gabysolomon Bacau
whooper Toronto ON	raul_
dechim Drobeta Turnu Severin	Amenthes