MySQL -- PHP -- XML

[corecreations]

I have a problem converting data retrieved from MySQL database into an xml format using php.

The xml file is used to build a navigation menu. This menu has 2 parts: a list of categories and their subcategories.

This is how the XML file should look:

http://www.corecreations.co.za/nav_menu/cat_menu.xml

Below is the structure of the database. I have two tables: category_tbl and subcategory_tbl.

category_tbl has three fields: cat_id, cat_name and cat_location
subcategory has three fields too: subcat_id, subcat_name and cat_id (foreign key)

Link to database setup:

http://www.corecreations.co.za/nav_menu/menu_db.sql

Please create the database menu_db and then import the tables and values.

The problem I am faced with is adding all subcategories, with the same cat_id, to the correct category. All the subcategories with cat_id = cat0001 should all be child nodes of category cat0001 i.e.

<cat_item cat_id = "cat0001">
<subcat_item cat_id = "cat0001" subcat_id = "subcat0001"/>
<subcat_item cat_id = "cat0001" subcat_id = "subcat0002"/>
<subcat_item cat_id = "cat0001" subcat_id = "subcat0003"/>
</cat_item>

I have tried for a while and now seek help if anyone can get this right.
Here is the php file, all I can get is link the first category with its subcategories.

PHP File: http://www.corecreations.co.za/nav_menu/menu.php
Thanks in advance.

Core Creations

[timvw]

i don't think performing an inner join and then interating over a resultset are really advanced...

[mx2k]

1) you might want to post some code
2) you might want to fix your connection string
3) and you usually can't just dl a php file when its running on a server (security issues and all that)
4) and out of curiousity, what are you using this in conjuction with that makes you want to pull the data into an xml file and then use it for the menu? will the users not have access to the xml file?

http://www.corecreations.co.za/nav_menu/menu.php
Warning: mysql_connect(): Access denied for user: 'username@localhost' (Using password: YES) in /home/httpd/vhosts/corecreations.co.za/httpdocs/nav_menu/menu.php on line 3
cannot reach database

[seanf]

Moved to a more appropriate forum. See Where should I post my thread? for details

Sean