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

[bpat1434]

FYI: Can't reach database.

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

~Brett

[bpat1434]

I apologize for the double post, but this is on a different note. Mods can combine if they really want to....

So you can output the data into xml format? Great. That's the hard part.

Here's what you do:

-- Query the database table category_tbl for all of the categories.
-- While the query is returned in an array, run a separate query to retrieve all the values that correspond to the current cat_id.
-- Loop as needed.

Example:

<?php
$query = "SELECT * FROM category_tbl GROUP BY cat_id ASC";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
    print "<cat_item cat_id='".$row['cat_id']."' cat_name='".$row['cat_name']."' location_id='".$row['location_id']."'>";
    $selsql = "SELECT * FROM subcategory_tbl WHERE cat_id = '".$row['cat_id']."' ORDER BY subcat_id ASC";
    $rslt2 = mysql_query($selsql);
    while($scat = mysql_fetch_array($rslt2)){
        print "<subcat_item cat_id='".$row['cat_id']."' subcat_id='".$scat['subcat_id']."' subcat_name='".$scat['subcat_name']."'/>";
    }
    print "</cat_item>";
}
?>

Hope that helps some.

~Brett