CODEWITHSUNDEEP
phpmysqlxmlrss
Rocco The Taco
Rocco The Taco

Reputation: 3777

Encoding Error when creating XML from PHP/MySQL query

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;

Upvotes: 0

Views: 701

Answers (1)

kums
kums

Reputation: 2691

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

Upvotes: 1

Related Questions