troubleshooting Question

Create dynamic XML table from MySQL using PHP

Avatar of DanielAttard
DanielAttardFlag for Canada asked on
PHPXMLMySQL Server
11 Comments2 Solutions426 ViewsLast Modified:
I am trying to use the code below to dynamically create XML data.  The code works on some tables, but fails on others.  It fails when parsing a line containing an ampersand (&), but only in certain browsers.  For example, the code seems to work fine in Safari, but in Chrome and Opera browsers I get this error:

XML parsing failed: syntax error (Line: 84, Character: 27)
...
83:   <MunicipalitiesRegion>1.00</MunicipalitiesRegion>
84:   <MunicipalityName>Alfred & Plantagenet</MunicipalityName>
85:   <MunicipalityType>Township</MunicipalityType>

Can you suggest a way to alter my code so that it will handle for invalid characters and create valid XML from all of my table data?  Thank you.

<?php 

header("Content-type: text/xml"); 

$host = "localhost"; 
$user = "root"; 
$pass = "root"; 
$database = "mydatabase"; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$query = "SELECT * FROM tbldata"; 

$resultID = mysql_query($query, $linkID) or die("Data not found."); 

$xml_output = "<?xml version=\"1.0\"?>\n"; 
echo $xml_output."<users>\n"; 
while ($line = mysql_fetch_assoc($resultID) ) {
  echo "<user>\n";
  foreach($line as $key=>$value)
    echo "  <$key>$value</$key>\n";
  echo "</user>\n";
}
echo '</users>'; 
?>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros