Encoding Error when creating XML from PHP/MySQL query

Question

I have the following code successfully querying and exporting dynamically from a MySQL to XML output. The only problem I am having now is that I am getting an encoding error and I have no idea how to track it down?

Here is a sample URL: http://progresstechnologies.com/xml/xmlExport.php

Code

//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "bb";
$config['mysql_pass'] = "bb";
$config['db_name']    = "db";
$config['table_name'] = "table";

//connect to host
mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
//select database
@mysql_select_db($config['db_name']) or die( "Unable to select database");

$xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$root_element = $config['table_name']."s"; //fruits
$xml         .= "<$root_element>";

//select all items in table
$sql = "SELECT * FROM table WHERE ListOfficeName LIKE 'Premier%' ";

$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

if(mysql_num_rows($result)>0)
{
   while($result_array = mysql_fetch_assoc($result))
   {
      $xml .= "<".$config['table_name'].">";

      //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.="</".$config['table_name'].">";
   }
}

//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;

Just a sidenote: After looking at your sample output, it's best to use \n (line break) after each line output such as and for example $xml .= "<$key>" . "\n"; which will give you a much cleaner (vertical) output rather than having it all in one (horizontal) line, which also makes it hard to read and harder to debug. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 7, 2014 at 18:50
Got it, thanks. I updated the code but it did not appear to make much of a difference on the test URL? — Rocco The Taco
– Rocco The Taco, Commented Nov 7, 2014 at 18:53
You're welcome Rocco. My comment would not have fixed the problem, it was just a suggestion. In regards to what is causing the issue, that I wasn't able to pinpoint. I'll keep looking at it, but I can't promise you anything. What "error" are you getting and are you using error reporting? — Funk Forty Niner
– Funk Forty Niner, Commented Nov 7, 2014 at 18:55
Okay, thanks. I'm getting a error on line 2420 at column 10: Encoding error when I visit the sample URL — Rocco The Taco
– Rocco The Taco, Commented Nov 7, 2014 at 18:56
You're welcome. If you could add the line breaks in your code as I suggested then do another test with a new file, then I or others will probably be able to tell a bit better as to where the offending line's problem could be. My browser wasn't able to read the entire line like that. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 7, 2014 at 18:58

kums · Accepted Answer · 2014-11-07 21:22:00Z

1

It looks like you are using MySQL's default latin1 charset for your database/table/column.

Solution 1: If you want the XML to be in latin1, you can fix the issue by changing

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

to

header ("Content-Type: text/xml; charset=latin1");

Solution 2: If you want the XML in UTF-8, modify the latin1 column of your MySQL table to UTF8 using

ALTER TABLE tblname MODIFY fldname TEXT CHARACTER SET utf8;

Note: In you want to use UTF-8 by default instead of latin1 charset for your MySQL database, read this: http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

answered Nov 7, 2014 at 21:22

kums

2,7012 gold badges16 silver badges16 bronze badges

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

1 Comment

Rocco The Taco Over a year ago

Thanks! I forgot to mark this as the answer, sorry!

Collectives™ on Stack Overflow

Encoding Error when creating XML from PHP/MySQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related