Exporting MySQL data to xml using PHP - HTML encoding and general error BRAIN MELT

Question

Anyone who can answer my question deserves a BIG FAT GOLD MEDAL OF AWESOMENESS!

I'm trying to get the contents of a MySQL table into a nice, easy XML format. I'm running a bit of PHP which works great and I can see XML (Good times). However, in the MySQL table there are a couple of fields which are populated with non-encoded HTML table code. I'm wrapping each field value that I get inside CDATA tags, I've made sure that the xml tags are being closed, but I'm wondering if I'm missing something because it's erroring and I can't see why (Bad times). It looked fine to me, so I tried to open this in Excel (as that's how the client will see it) it claimed that the start tag of "package" was matched with an end tag of "long_description".

http://www.shavesgreensafaris.com/display.php is the page that I'm working on so you can see the data there.

This is the code I'm using...

        $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
        $root_element = "packages";
        $xml         .= "<$root_element>";


        if(mysql_num_rows($result)>0)
        {
           while($result_array = mysql_fetch_assoc($result))
           {
              $xml .= "<package>";

              //loop through each key,value pair in row
              foreach($result_array as $key => $value)
              {
                 //$key holds the table column name
                 $xml .= "<$key>";

                 //embed the SQL data in a CDATA element to avoid XML entity issues
                 $xml .= "<![CDATA[$value]]>"; 

                 //and close the element
                 $xml .= "</$key>";
              }

              $xml.="</package>";
           }
        }

//close the root element
$xml .= "</$root_element>";

//send the xml header to the browser
header ("Content-Type:text/xml"); 

//output the XML data
echo $xml;

What ON EARTH am I doing wrong?!

EDIT

OK, so there appears to be something that removes one "<" from a long_description tag around line 310 - you can search in the view source if you want to see it as ">long_description>" - something really weird has happened and the tag isn't properly formed. I wasn't entirely sure HOW this could happen as the code I'm using definitely puts opening and closing angle brackets on all $keys. It only happens once, but it seems to stuff everything else up.

Anyone know why this might be occurring?

Any help HUGELY appreciated, and thanks in advance!

Gem

Are you sure, the xml is working? it returns XML Parsing Error: not well-formed near 3 X day Stag hunt. — Sanjay
– Sanjay, Commented Jun 22, 2012 at 11:00
yes, there is a problem on the last but one package (Red stag package) - the <cost> and <fees> elements are not well-formed — paul
– paul, Commented Jun 22, 2012 at 11:06
(sorry, mashed the keyboard...) the long_description around this line: ...daily rate.</p>]]></long_description><short_description><![CDATA[<p>This 10... Weird... — Gem
– Gem, Commented Jun 22, 2012 at 11:08

DaveRandom · Accepted Answer · 2013-03-04 10:12:24Z

2

I feel that DOM would be the way to approach this. The code may look somewhat more cumbersome at first glance, but it will take care of all the output formatting for you:

<?php

  // The names of the root node and the node that will contain a row
  $root_element = "packages";
  $row_element = "package";

  // Create the DOMDocument and the root node
  $dom = new DOMDocument('1.0', 'utf-8');
  $rootNode = $dom->appendChild($dom->createElement($root_element));

  // Loop the DB results
  while ($row = mysql_fetch_assoc($result)) {

    // Create a row node
    $rowNode = $rootNode->appendChild($dom->createElement($row_element));

    // Loop the columns
    foreach ($row as $col => $val) {

      // Create the column node and add the value in a CDATA section
      $rowNode->appendChild($dom->createElement($col))
              ->appendChild($dom->createCDATASection($val));

    }

  }

  // Output as string
  echo $dom->saveXML();

edited Mar 4, 2013 at 10:12

answered Jun 22, 2012 at 11:28

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gem Over a year ago

If I could demonstrate all the ways I love you right about now, I ruddy would. Thanks, this has made light of what was fast becoming a world of pain. ALL HAIL DAVERANDOM etc.

Collectives™ on Stack Overflow

Exporting MySQL data to xml using PHP - HTML encoding and general error BRAIN MELT

EDIT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related