Using DataAssist Query to export to XML
I have a DataAssist query that is working.
I have a fwrite to XML export process using same DataAssist query working, but not as desired.
<?php
mysql_select_db($database_dbconnect, $dbconnect);
$query_Recordset1 = "SELECT * FROM mytable GROUP BY name";
$Recordset1 = mysql_query($query_Recordset1, $dbconnect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<?php
$fp = fopen('example.xml', 'w+');
fwrite($fp, '<?xml version="1.0" encoding="utf-8" ?>');
fwrite($fp, '<content>');
?>
<?php do {
fwrite($fp, '<item Name="'.$row_Recordset1['name'].'">');
fwrite($fp, '<image="'.$row_Recordset1['image'].'">');
fwrite($fp, '<text>');
fwrite($fp, '<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>');
fwrite($fp, '</text>');
fwrite($fp, '</image>');
fwrite($fp, '</item>');
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
?>
<?php
fwrite($fp, '</content>');
fclose($fp);
?>
What I expect as a result is something like this...
<?xml version="1.0" encoding="utf-8" ?>
<content>
<item Name="FRUIT">
<image="apple.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
<image="orange.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
<image="pear.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
</item>
<item Name="Animals">
<image="dog.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
<image="cat.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
</item>
<item Name="Flowers">
<image="rose.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
<image="daisy.jpg">
<text>
<![CDATA[Lorem ipsum dolor sit amet, consectetuer.]]>
</text>
</image>
</item>
</content>
If I use ORDER BY in stead of GROUP BY I get something closer to what I'm looking for, but it needs only to send the <item> only once per/match.
I think my query is the problem... What I'm getting instead of the above desired result is only the first record of each ("GROUP BY") <item> name. Which is exactly what the query is asking for...I need to write this XML file to match up all of the records that have the same names, write those records close that </item> and then move to the next set of <item> name matches and so on until the query is done.
What am I missing here?