multi level SQL results into XML

[Damjan]

Hi All;

this is not a specific PHP question, but as I am working in PHP....

My SQL queries return one, two, sometimes even three levels of XML
data.
So for orders in my system I would like to see in my XML:

<RESULTS>
<order>
<number>3213</number>
<date>Feb 03, 2003</date>
<product>
<name>NEC Monitor</name>
<quantity>3</quantity>
<serial_numbers>
<sn>12321312321</sn>
<sn>44314133422</sn>
<sn>43434343553</sn>
<serial_numbers>
</product>
<product>
<name>Genius Mouse</name>
<quantity>2</quantity>
<serial_numbers>
<sn>23232</sn>
<sn>44343</sn>
<serial_numbers>
</product>
</order>
<order>
<number>444</number>
<date>Mar 06, 2003</date>
<product>
<name>MS Keyboard</name>
<quantity>1</quantity>
<serial_numbers>
<sn>333333</sn>
<serial_numbers>
</product>
</order>
.........
</RESULTS>

obviously my query would reutrn

order_num order_date product_name quantity
serial_number
------------------------------------------------------------------------
3231 Feb 03, 2003 NEC Monitor 3 12321312321
3231 Feb 03, 2003 NEC Monitor 3 44314133422
3231 Feb 03, 2003 NEC Monitor 3 43434343553
3231 Feb 03, 2003 Genius Mouse 2 23232
3231 Feb 03, 2003 Genius Mouse 2 44343
444 Mar 06, 2003 MS Keyboard 1 333333

You get the point. I do not want to have 3 queries, but only one that
returns something like described above.

For now I have a functioin that does this, but the algorithm is not
the best, and it only works for two levels (so XML without the s/n for
example).
This must be a common problem, I would appriciate input as to how this
is commonly done, any pointers to web sites, articles... are all
welcome. I'd try to search the groups but for what?:) I would also
like to describe my current algorithm, but for now it seems I would
just bore you.

Looking for some input

Damjan

[Jim Dabell]

Damjan wrote:

[snip]

My SQL queries return one, two, sometimes even three levels of XML
data. [snip] You get the point. I do not want to have 3 queries, but only one that
returns something like described above.

[snip]

You want a method of returning a sorted tree from SQL, right? Try this:

<URL:http://simon.incutio.com/archive/2003/06/19/storingTrees>

--
Jim Dabell

[Damjan]

Jim Dabell <ji********@jimdabell.com> wrote in message news:<Iu********************@giganews.com>...

Damjan wrote:

[snip]

My SQL queries return one, two, sometimes even three levels of XML
data.

[snip]

You get the point. I do not want to have 3 queries, but only one that
returns something like described above.

[snip]

You want a method of returning a sorted tree from SQL, right? Try this:

<URL:http://simon.incutio.com/archive/2003/06/19/storingTrees>

That's a cool site, and I've read already some about that, but
unfortunately that is not what I need.

THanks anyway

Damjan