I want to redesign the xml structure

my case is that i don't able to write the code please tell me..

i want to show in the xml that 1 product -> 1 Category -> many subCategory if category change then 1 product -> 2nd category -> 2 SubCategories etc.. product is always 1 product not changed..

1 product based xml.

please help me...

following are the current structure:

<?
include_once("./inc/config.php");
include_开发者_运维技巧once("jsAdminMethods.php");
$productID = $_POST['productID'];

//echo $productID;

$productQuery = mysql_query("select a.productId, a.productName, a.productAbout, a.productPic, aa.categoryID, aa.categoryName, aa.categoryDiscription,  aa.categoryPic, l.subCategoryID, l.subCategoryName, l.subCategoryDescription from category aa, product a, subCategory l where a.productID = aa.productID and l.categoryID = aa.categoryID and a.productID = $productID");
$num = mysql_num_rows($productQuery);

if(!$num == 0 ){
    $_fileName ="";

    $file= fopen("../xml/{$productID}.xml" , "w"); 


    $_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>";
    $_XML = "<!DOCTYPE Server SYSTEM \"opt/pdos/etc/pdoslrd.dtd\">";

    $_xml .="<cmm>";


     while ($row = mysql_fetch_array($productQuery, MYSQL_ASSOC)) { 

        $_xml .=" <product>";
        $_xml .="   <productID>" . $row['productId'] . "</productID>";
        $_xml .="   <productName><![CDATA[" . htmlspecialchars($row['productName'],ENT_QUOTES) . "]]></productName>";   
        $_xml .="   <productDescription><![CDATA[" . htmlspecialchars($row['productAbout'],ENT_QUOTES) . "]]></productDescription>";    
        $_xml .="   <productPicture><![CDATA[" . htmlspecialchars($row['productPic'],ENT_QUOTES) . "]]></productPicture>";
        $_xml .=" <category>";
        $_xml .="   <categoryID>" . $row['categoryID'] . "</categoryID>";
        $_xml .="   <categoryName><![CDATA[" . htmlspecialchars($row['categoryName'],ENT_QUOTES) . "]]></categoryName>";    
        $_xml .="   <categoryDescription><![CDATA[" . htmlspecialchars($row['categoryDiscription']) . "]]></categoryDescription>";  
        $_xml .="   <categoryPicture><![CDATA[" . htmlspecialchars($row['categoryPic'],ENT_QUOTES) . "]]></categoryPicture>";               
        $_xml .=" <subCategory>";
        $_xml .="   <subCategoryID>" . $row['subCategoryID'] . "</subCategoryID>";
        $_xml .="   <subCategoryName><![CDATA[" . htmlspecialchars($row['subCategoryName'],ENT_QUOTES) . "]]></subCategoryName>";   
        $_xml .="   <subCategoryDetail><![CDATA[" . htmlspecialchars($row['subCategoryDescription'],ENT_QUOTES) . "]]></subCategoryDetail>";                
        $_xml .=" </subCategory>";
        $_xml .=" </category>";
        $_xml .=" </product>";

    }
    $_xml .="</cmm>";
    fwrite($file, $_xml);
    fclose($file);


    $value = "product XML Generted Successfully";
    $tagproductXML = md5($value);
    header("location:xmlGenerator.php?tagproductXML=$tagproductXML");
}

else{
      echo "No Records found in xml";   
}

?>

---

Stylistic tip, if you're on PHP 5.3:

use heredocs and an anonymous function to save yourself all those hideous string concatentations:

$x = function($txt) {
    return(htmlspecialchars($txt));
}

and then

while($row ...) {
   $_xml .= <<<EOL
<product>
   <productID>{$row['productID']}</productID>
   <productName>{$x($row['productName'])}</productName>
    etc...
EOL;
}

Notice how I've replace the direct calls to htmlspecialchars() with the anonymous function $x, and used it within the HEREDOC to escape the text as the string's being built. Using this type of construct saves you from having to do the line after line after line of string concatenation, relieves you of the burden of having to escape quotes within the string you're building, and with a proper syntax-highlighting editor, is far easier to read than the original.

---

I'm not sure I fully understand the question, but I'll take a stab at it.

i want to show in the xml that 1 product -> 1 Category -> many subCategory if category change then 1 product -> 2nd category -> 2 SubCategories etc.. product is always 1 product not changed..

It sounds like you're saying that one Product may be in multiple Categories. You want all of that Product's Categories (and their Subcategories) inside the <product> tag.

Given that your data set will only ever contain one Product, this only requires some small changes to your loop.

 $seen_first_row = false;
 while ($row = mysql_fetch_array($productQuery, MYSQL_ASSOC)) { 
    if(!$seen_first_row) {
        $seen_first_row = true;
        $_xml .=" <product>";
        $_xml .="   <productID>" . $row['productId'] . "</productID>";
        $_xml .="   <productName><![CDATA[" . $x($row['productName'],ENT_QUOTES) . "]]></productName>";   
        $_xml .="   <productDescription><![CDATA[" . $x($row['productAbout'],ENT_QUOTES) . "]]></productDescription>";    
        $_xml .="   <productPicture><![CDATA[" . $x($row['productPic'],ENT_QUOTES) . "]]></productPicture>";
    }
    // Category and Subcategory XML here
}
$_xml .=" </product></cmm>";

There are two changes here. First, a new variable: $seen_first_row. When you start fetching results from the database, that variable is checked. If it's false, which it will be when the loop starts the first time, the opening <product> tag is added. The variable is then set to true. The next time the loop starts, the opening tag won't be printed.

The second change is that the closing </product> tag has been removed from the loop and placed outside.

The result of these changes is that there will be only one opening <product> tag, containing product details, while there may be multiple <category> tags, depending on the data that comes back from the database.

Also, in addition to @Marc B's excellent hint about (ab)using PHP 5.3 closures, you should be aware of one more thing: Your code contains an SQL Injection vulnerability. You are taking input directly from the user and passing it to your SQL without properly escaping it. Because you're using the clunky and decrepit "mysql" extension, you only have one viable option: mysql_real_escape_string. You may use it thusly:

$productQuery = mysql_query(
    "select ... where ... and a.productID = " 
    . mysql_real_escape_string($productID)
);

It is critically important that you properly escape all user-provided database input. Other, more modern database adapters can give you easy, advanced, automatic protection. Please consider switching to PDO or mysqli long-term.

---

Answering your follow-up about categories.

First, you'll want to add an ORDER BY clause to your query, set to sort on the categoryID column.

Next, you need to modify the loop. Initialize a new variable to hold the last row's categoryID. Set it to null outside of the loop.

Inside the loop, after the do-we-need-a-<product>-tag check, check the current categoryID. If it's different than the last categoryID, we need to do two things:

1. If the last categoryID is not null, that means we're done processing a previous category now, and should emit a </category> closing tag.
2. If the last categoryID is different than the current categoryID, then we need to open up a new <category> tag and give our details.

Following those two checks will come the expected subcategory XML. After printing the subcategory, *set the last categoryID to the current categoryID.

You'll also need to modify the last closing tags, adding a </category>.

Note that this set of logic requires that all categoryIDs in the result set not be null. If you get null categories, you will need to update the close-and-reopen logic accordingly.

(You might want to take the time to give yourself a refresher on looping and conditional logic; it's very possible that you have a knowledge gap in that area. Normally these topics are covered by tutorials and educational material before things like SQL and XML.)