[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create dynamic XML table from MySQL using PHP

Posted on 2009-12-23
11
Medium Priority
?
396 Views
Last Modified: 2012-05-08
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>'; 
?>

Open in new window

0
Comment
Question by:DanielAttard
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 6

Expert Comment

by:mmarth
ID: 26117385
immediately before you echo, try:
$value = str_replace(array('&amp;','&#38;'),'&',htmlentities($value));
0
 

Author Comment

by:DanielAttard
ID: 26117412
Thanks for the suggestion.  Where exactly would I put this line?  Before the first echo, or each and every echo?
0
 
LVL 21

Expert Comment

by:K V
ID: 26117481
Do you know:--
mysqldump -uroot -ppass  --xml DB tablename > tablename.xml
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 800 total points
ID: 26117574
I'm not sure why mmarth is doing a str_replace after an htmlentities. It seems to me that it will undo what the htmlentities did to the &.

Anyway, try this...

$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>". htmlentities($value) . "</$key>\n";       // <-- altered line
  echo "</user>\n";
}
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26119014
This is a common issue.  Character #27 is right between Alfred and Plantagenet.

<MunicipalityName>Alfred & Plantagenet</MunicipalityName>

The ampersand is not allowed in XML and must be "entitized" into the string & a m p ;

FWIW, I see this kind of malformed XML all the time, and SimpleXML barks about it.  Some good information about this issue and other characters that need entity encoding here:
http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references
0
 
LVL 6

Expert Comment

by:mmarth
ID: 26119244
bportlock, sometimes i wonder what i am doing too. good job.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26119610
@mmarth - it happens to us all eventually.

BTW Merry Christmas to everyone.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1200 total points
ID: 26119776
Just a thought.. This seems like it might be more on target than htmlentities()
http://us3.php.net/manual/en/function.htmlspecialchars.php

Merry Christmas to all! ~Ray
0
 

Author Comment

by:DanielAttard
ID: 26123395
Hey Ray - Thanks for the help.  Your suggestion worked for the ampersand (&), but now I see that I am getting another error with the accent character - é.  Is there some way I can alter the code to handle for all problem characters?
0
 

Author Comment

by:DanielAttard
ID: 26123534
You were right Ray.  I started using htmlspecialchars.php and this fixed my problem.  

p.s.  Thanks also to the link on Peter Norvig about learning computers in 10 years.  Great read.  
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26127941
Thanks for the points - it's a great question, ~Ray
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question