Hi all,
Thanks for taking the time to read my post.

I'm outputting XML from a MySQL database, and my query is output as(Ignore columns 2, 3 and 4.):

http://i50.tinypic.com/2ij4vtu.gif

If an emotion has a subanswer, it then should display the gateways/categories under that subname in <sub> tags, and end with an </opt> tag at the end of the subs under that subname group. If the subname is blank, it should not have sub tags and have <opt /> tags, without <sub> tags.

If there are different <subname> it would add <opt /> tags as appropriate.


My current PHP:

PHP Code:
<?php
require_once('../req/dbfunctions.php');
header("Content-type: text/xml");

$xml_output "<?xml version=\"1.0\"?>\n"
$xml_output .= "<emotions>\n"

dbConnect();
$result mysql_query("SELECT emotion.emotionName AS emotionName, emotion.emotionCode AS emotionCode, emotion.emotionDescription as emotionDescription, emotion.emotionQuestion as emotionQuestion, subemotion.emotionName AS subemotionName, emotionSubAnswer.emotionSubAnswerName As subanswer, category.categoryName as categoryName, category.categoryCode as categoryCode, gateway.gatewayName as gatewayName, gateway.gatewayCode as gatewayCode
FROM emotion AS emotion
LEFT OUTER 
JOIN subEmotion ON subEmotion.parentID = emotion.emotionID
LEFT OUTER 
JOIN emotion AS subemotion ON subemotion.emotionID = subEmotion.emotionID
LEFT OUTER 
JOIN emotionLink on emotion.emotionID = emotionLink.emotionID
LEFT OUTER 
JOIN emotionSubAnswer on emotionSubAnswer.emotionSubAnswerID = emotionLink.emotionSubAnswerID
LEFT OUTER 
JOIN category on category.categoryID = emotionLink.categoryID
LEFT OUTER
JOIN gateway on gateway.gatewayID = emotionLink.gatewayID
ORDER BY emotion.emotionName, subemotion.emotionName, emotionSubAnswer.emotionSubAnswerName, category.categoryName, gateway.gatewayName
"
);


$previousEmotion "";
$previousSubEmotion "";
$previousPath "";
$previousSubAnswer "";
$options "";
$related "";

while(
$row mysql_fetch_assoc($result)) 
{       
    
    if (
$row['emotionName'] != $previousEmotion) {  
        if (
$previousEmotion != "") {
            
            
$options .= "</opt>";
            
$options .= "</options>";
            
$related .= "</related>";
            
$xml_output .= $options $related "</emotion>";         
            
$options '';
            
$related '';  
            
$subanswer '';
        }
        
$xml_output .= "<emotion>";
        
$xml_output .= "<label>" $row['emotionName'] . "</label>";
        
$xml_output .= "<id>" $row['emotionCode'] . "</id>";
        
$xml_output .= "<description><![CDATA[" $row['emotionDescription'] . "]]></description>";
        
$options "<options>";
        
$options .= "<opt>";
        
$related "<related>";
    }
    
    
    
// Mostly from here
    
    
    
    
if ($row['subanswer'] != "") {
        if (
$row['subanswer'] != $previousSubAnswer) {
    
$options .= "<name>" $row['subanswer'] . "</name>";
        }
    
$options .= "<sub>";
    }
    if (
$row['categoryName'] != "") { 
        
$options .= "<name>" $row['categoryName'] . "</name><linkid>" $row['categoryCode'] . "</linkid><linktype>choice</linktype>";
    }
    else if (
$row['gatewayName'] != "") {
        
$options .= "<name>" $row['gatewayName'] . "</name><linkid>" $row['gatewayCode'] .  "</linkid><linktype>gateway</linktype>";
    }
    if (
$row['subanswer'] != "") {
    
$options .= "</sub>";
    }
    
    
    
// To here
    
    
    
    
$previousEmotion $row['emotionName']; 
$previousSubEmotion $row['subemotionName']; 
$previousPath $row['emotionName'];
$previousSubAnswer $row['subanswer'];
}
if (
mysql_num_rows($result) > 0)
{
    
$xml_output .= $options;
    
    
$xml_output .= "</opt>";
    
$xml_output .= "</options>";
    
$xml_output .= $related;
    
$xml_output .= "</related>";
    
$xml_output .= "</emotion>";         
}  

dbClose();
$xml_output .= "</emotions>"

echo 
$xml_output;
?>
My current XML output (fragment for 1 emotion i'm having trouble with:

Code:
- <emotion>
  <label>Happy</label> 
- <options>
- <opt>
  <name>Relationships</name> 
  <linkid>SALIRE</linkid> 
  <linktype>choice</linktype> 

   // There should be an ending </opt> tag and a new <opt tag should be started

  <name>Health</name> 
- <sub>
  <name>Energy</name> 
  <linkid>ENERGY</linkid> 
  <linktype>gateway</linktype> 
  </sub>
- <sub>
  <name>Confused at work</name> 
  <linkid>CONAMW</linkid> 
  <linktype>choice</linktype> 
  </sub>
  </opt>
  </options>
  <related /> 
  </emotion>
I'm just about there with the output, it's just this last little part i'm having trouble working out adding in the <opt></opt> tags in the right places.

Any help would be appreciated as this is the last thing i need to do before it's finished.

Thanks for reading and looknig forward to your reply.

Martin.