How to create and access xml with attributes in Flex

Question

so my problem is, that I'm trying to make an application, that will mostly retrieve data from a MySQL database, divided into tables with particular date the data was created. Right now, I have a script for PHP, that allows me to extract the data from the database and put it into an XML file. The problem with that is, that I don't have it date-ordered, which is very important in the app, but I don't have any idea, how would I make a script, that would extract the data from MySQL db and put the data into this format:

<allitems>
    <items date="29-11-2011">
        <item>
            <title>Something</title>
            <title2>Something else</title2>
        </item>
        <item>
            <title>Something2</title>
            <title2>Something else2</title2>
        </item>
        <item>
            <title>Something3</title>
            <title2>Something else3</title2>
        </item>
    </items>
</allitems>

Whereas there would be more groups, each with different date attribute, and each created from a different table, but all this data in one XML file.

By the way, this is my PHP script for what I use right now:

$query = mysql_query("SELECT * FROM test_codes");
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<items>";

$fp = fopen($filename,"w");

for($x = 0 ; $x < mysql_num_rows($query) ; $x++){
    $row = mysql_fetch_assoc($query);

    $xml_output .= "\t<item>\n"; 
    $xml_output .= "\t\t<title>" . $row['title'] . "</title>\n";
    $xml_output .= "\t\t<code>" . $row['code'] . "</code>\n";
    $xml_output .= "\t\t<quantity>" . $row['quantity'] . "</quantity>\n"; 
    $xml_output .= "\t\t<price>" . intval($row['price'])*intval($row['quantity']) . "</price>\n"; 
    $xml_output .= "\t</item>\n";
}

$xml_output .= "</items>";

fwrite($fp,$xml_output);
fclose($fp);

And this is a picture, of how I mean the data usage in my flex application (in a list): http://img855.imageshack.us/img855/9291/sofxml.png

Answer 1

// query ordered by date
$query = mysql_query("SELECT * FROM test_codes ORDER BY 'date' DESC");
$orderedResult = array();

// group the items by date
$queryResult = mysql_fetch_asoc($query);
foreach($queryResult as $row) {
  $orderedResult[$row['date']][] = $row;
}

// render output by date
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<allitems>";

$fp = fopen($filename,"w");

foreach($orderedResult as $key => $dateItems) {
  $xml_output .= '<items date="' . $key . '">';
  foreach($dateItems as $row) {
    $xml_output .= '<item>';
        $xml_output .= '<title>' . $row['title'] . '</title>';
        $xml_output .= '<code>' . $row['code'] . '</code>';
        $xml_output .= '<quantity>' . $row['quantity'] . '</quantity>';
    $xml_output .= '</item>'
  }
  $xml_output .= '</items>';
}

$xml_output .= "</allitems>";

fwrite($fp,$xml_output);
fclose($fp);

Answer 2

Suppose you have a php script which pulls the data from mySQL and responds with a xml (like you have given in your question) at http://myserver.com/myphp.php?arg1=val1&arg2=val2

You'll use a HTTPService to get that

private var service:HTTPService;

//Run this on application initialize
private function init() {
    service=new HTTPService();
    service.addEventListener(ResultEvent.RESULT, sResult);
    service.resultFormat="xml";
    service.url="http://myserver.com/myphp.php?arg1=val1&arg2=val2";
}

private function sResult(e:ResultEvent):void {
    var o:Object = xmlStringToObject((e.result as XMLNode).toString());
    //Now you have your xml as an object
    trace(o.allitems.items.getItemAt(0).date); //gives 29-11-2011
    trace(o.allitems.items.getItemAt(0).item.getItemAt(0)); //{title: Something, title2: Something else}
    //You get the pattern, if not, debug and watch o
}

public function xmlStringToObject(xmlStr:String):Object{
    var xmlDoc:XMLDocument = new XMLDocument(xmlStr);
    xmlDoc.ignoreWhite=true;
    var decoder:SimpleXMLDecoder = new SimpleXMLDecoder(true);
    var resultObj:Object = decoder.decodeXML(xmlDoc);
     return resultObj;
}