mySQL - PHP (problem) - XML - Flash

[photofx]

Hello good people

Basically I need to spit out an xml page to create a flash menu. I'm almost there but for my if else statements, I think. It's coming out all pear shaped. Please would someone guide me here in newbie fashion, this looked easier than it is. I'll forge ahead with my problem.

The structure of the existing mySQL table is id_category, category, name, description. Category is "0" when top level. When it is a sub category "category" is the same number as id_category of the parent record. The parent record can be a subcategory. I am going a max of 3 levels.

I have inc_sql.php:

Code: Select all

<?php
define ("USERNAME","root");
define ("PASSWORD","");
define ("HOSTNAME","localhost");
define ("DATABASE","");
?>

Then Categories.php: All is working fine until we get to the xml output and if's and else's and well I'm and

Code: Select all

<?php

//Include database variables
include_once("inc_sql.php");
//Connect to database
$connect = mysql_connect(HOSTNAME, USERNAME, PASSWORD);
mysql_select_db(DATABASE, $connect);

$result = mysql_query("SELECT categories . name AS name , subitem . name AS subitem , subsubitem . name AS subsubitem " .
"FROM categories AS subsubitem RIGHT JOIN ( categories AS subitem RIGHT JOIN categories ON subitem . category = categories . id_category ) ON subsubitem . category = subitem . id_category " .
"WHERE ( ( ( categories . category ) = 0 ) ); "); 

echo '<?xml version="1.0" encoding="ISO-8859-1"?>';
echo '<menus>';
while($row=mysql_fetch_array($result)){
$line = '<button name="'.$row[name].'">';

if ($row[subitem] == NULL){
echo '</button>';
}
else {
echo '<subitem name="'.$row[subitem].'">';
}
if ($row[subsubitem] == NULL){
echo '</subitem></button>';
}
else {
echo '<subsubitem name="'.$row[subsubitem].'"/></subitem></button>';
}
echo $line;
}
echo '</menus>';
?>

This is the result:

<?xml version="1.0" encoding="ISO-8859-1"?><menus><subitem name="Sportswear"><subsubitem name="Sun Creams"/></subitem></button><button name="Accessories"></button></subitem></button><button name="New Category"></menus>

Where it should be:

<?xml version="1.0" encoding="ISO-8859-1"?>
<menus>
<button name="Accessories">
<subitem name="Sportswear">
<subsubitem name="Sun Creams"/>
</subitem>
</buttom>
<button name="New Category"/>
</menus>

If a button has a subitem the subitem must be enclosed with the button tag. Simarly with subsubitem it must be between the subitem tag. These are basically categories, subcategories and subsubcategories - max 3 levels.

The sql statement spits out the following with some joins and aliases:

name subitem subsubitem
Accessories Sportswear Sun Creams
New Category Null Null

Null would be where there is no sub or subsub category. There could be a subcategory and no subsubcategory.

Best regards
Kevin

[photofx]

OK never mind I managed to get it.

After about a million lines and fiddles I discovered it would have never worked with that query. Anyway I started from scratch and have it sorted - yeah!

Sometime you can't see the wood for the trees. It takes a huge hiccup to realise you need to take a step back and re-evaluate the situation.

ciao!

PS Have a fantastic day (& the weekend too).

[1840dsgn]

If you could post up your solution that would be great so people can benefit from it in the future!

Mike

[photofx]

I can do indeed, though I think it's rather specific. I cleaned it up and added some comments.

It uses the following mySQL information:

Table: categories
Fields: id_category, category, name

The table exists from a separate application, I wanted to create a menu with xml from the categories information. Note: the category field, in this case, must equal the id_category to become a child(sub) item of that category.

You could always change the outputs to reflect an a href link, and indent the output of the sub and subsub categories with a hyphen or style with css.

categories.php:

Code: Select all

<?php
// Include database variables and function
include_once("inc_sql.php");
?>

<?php
// Connect to database
$connect = mysql_connect(HOSTNAME, USERNAME, PASSWORD);
mysql_select_db(DATABASE, $connect);
    // Query the Database 
    $sql['categories']= "select `id_category`, `name` from categories " .
    "where `visible`='1' and `category`=''";
    $categories = mysql_query($sql['categories']);
    // Start to output xml
    echo '<?xml version="1.0" encoding="ISO-8859-1"?>';
    echo '<menus>';
    
    // Cycle through the Categories and show their Sub-Categories
    while ($data['category'] = mysql_fetch_assoc($categories)) {
    
    // Start displaying data
    echo "<button name =\"{$data['category']['name']}\" " .
    "location=\"category.php?id_category={$data['category']['id_category']}\">";
    
    // Use Function to loop through sub-Categories
    subCategory($data['category']['id_category'], 1, "subdata");
    
    // Finish off xml. 
    echo '</button>';
    }
    echo '</menus>'        
?>

inc_sql.php:

Code: Select all

<?php

// Keep the xml clean
error_reporting(0);

// Here You must define a user, password and host to match your needs.
define ("USERNAME","");
define ("PASSWORD","");
define ("HOSTNAME","");
define ("DATABASE","");

// Function on Original Query
function subCategory($id, $c, $type = "subdata") {

    // Query the Database
    $subcat = mysql_query("select id_category, name from categories where " .
    "category='{$id}' and visible='1'");

    // Start loop on subCategories
    while ($subdata = mysql_fetch_assoc($subcat)) {

    // If subsub data exists 
    switch ($type) {
    case "subdata":
        // Output Information
        echo "<subitem name =\"{$subdata['name']}\" location =\"category.php?id_category={$subdata['id_category']}\">" .
        str_repeat("", $c) . "";
        // Grab Sub-Sub Categories
        subCategory($subdata['id_category'], $c+1, "subsubdata");
        echo '</subitem>';
    break;
    case "subsubdata":
        // Output Information
        echo "<subsubitem name =\"{$subdata['name']}\" location =\"category.php?id_category={$subdata['id_category']}\"/>" .
        str_repeat("", $c) . "";
    break;
    }
  }
}
?>