Hi there guys

Stock again. 😕 😕

I need to create a XML file from to related table from Mysql.

I have a shopping cart where I like to out put the cart into XML.

Table one is the invoice details like ( Invoice Number, Total, Clients detail, the usual )

Table 2 has products that are purchased with their details which has the reference in each row same as the reference in table one for relation.

How could I or how should I construct the XML File so it could be retrieved back in the future.

Each XML file for each invoice is an individual file with the invoice number as file name.

Please help.

B.E-N

    The basic process can be the same as outputting database info in HTML, the only difference is the tags and the other mark-up details applicable to XML. The other method would be to use the [man]SimpleXML[/man] functions to build the XML file from your query data, using the SimpleXMLElement->asXML() function.

      I am using DW-CS3 which I can extract from Mysql to XML File, The only thing is how would I structure the XML file from two table as I mentioned in the original question?

      How could I see the main part of the invoice on top and the lines of purchased product on the bottom of the invoice retrieved to HTML for viewing.

      Remember two related tables in Mysql to only one XML file.

      Thanks

        I don't use DW, so have no idea how that fits into the picture.

        Is there a particular XML mark-up protocol you need to follow, or are you just looking for a way to store data in XML files? And if it's the latter, one might ask: why bother when the database is a much more efficient data storage mechanism? I'm not saying it's not the right thing to do, I just hesitate to say "do it like this" when I don't understand the underlying functional requirement for doing it in the first place.

          O.K
          I use DW and ?I said that because it give this ability to call Mysql table and generates a XML file using Spry which does XML export.

          Now I like to get the XML file of the Invoice because client has Flex based program that they like to download the XML to use locally and also like to have a look at it online.

          I just wanted to know how could I combine these two tables and generate one XML file. What would the structure be like. I do not necessarily have to use what DW gives me as an option to generate the XML.

            You could get all the data in one query, perhaps using a "union", e.g.:

            $inv = (int)$_POST['invoice_number'];
$sql = "
(SELECT * FROM table_1 WHERE invoice_number = $inv)
UNION
(SELECT * FROM table_2 WHERE invoice_number = $inv)
";

            What I have no idea is whether the DW functionality you use will be smart enough to extrapolate that into a usable XML format.

            If I were doing it (manually), I'd probably so something like:

            // start of xml file (ugly concatenation is to avoid confusion with php tags)
$xml = '<'.'?xml version="1.0" encoding="utf-8"?'.">\n<invoice>\n"; 

// first part
$sql = "SELECT * FROM table_1 WHERE invoice_number = $inv";
$result = mysql_query($sql) or die(mysql_error()."<pre>$sql");
$row = mysql_fetch_assoc($result)
foreach($row as $key => $val)
{
   $xml .= "<$key>".htmlspecialchars($val)."</$key>\n";
}

// second part
$sql = "SELECT * FROM table_2 WHERE invoice_number = $inv";
$result = mysql_query($sql) or die(mysql_error()."<pre>$sql");
while($row = mysql_fetch_assoc($result))
{
   $xml .= "<item>\n";
   foreach($row as $key => $val)
   {
      $xml .= "<$key>".htmlspecialchars($val)."</$key>\n";
   }
   $xml .= "</item>\n";
}
$xml .= "</invoice>";

// now do whatever you want with the string $xml...

              Oh Thanks

              I will try this and will let you know how went.

              Thanks for this again

                NogDog

                Mate I tried both ways, with DW and Manually your way and it is given me that table having different columns.

                Any Idea

                  Thanks NogDog, I did the manual way and it worked fine. Thanks Again. I appreciate it🙂 🙂 🙂

                    Write a Reply...