0

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;
Rocco The Taco
  • 3,695
  • 13
  • 46
  • 79
  • 1
    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 Nov 07 '14 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 Nov 07 '14 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**](http://php.net/manual/en/function.error-reporting.php)? – Funk Forty Niner Nov 07 '14 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 Nov 07 '14 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 Nov 07 '14 at 18:58
  • After looking at it again, there's an offending character at the beginning `<![CDATA[You cant` which seems to be some sort of apostrophe between the `n` and `t` or unicode `#65535;` which in this comment, Stack isn't showing. Using prepared statements could probably fix it and/or escaping your data with your present code. That is what seems to be breaking your code right now. – Funk Forty Niner Nov 07 '14 at 19:03
  • Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. – Jay Blanchard Nov 07 '14 at 19:04
  • Furthermore, I think it may have something to do with a character set. See this Q&A on Stack http://stackoverflow.com/q/3245809/ there's a mention about it. This `` is what's being injected, or seems to be. Where you have `You can't` replace with `You cant` and you should get success or near success. – Funk Forty Niner Nov 07 '14 at 19:08
  • I think it might be something in the data. When I omit the PropertyInformation column from the query it works fine – Rocco The Taco Nov 07 '14 at 19:23
  • I think it is in the data, there is what seems to be an apostrophe of sorts which is breaking your code. Go over some of my comments above. You will need to somewhat escape your data. – Funk Forty Niner Nov 07 '14 at 19:27
  • I found it...it's this strange character that looks like a long dash I tried to replace it with this query but it is not working. Even more strange is when I paste the query here it shows something entirely different. SELECT MLSNumber, REPLACE(PropertyInformation, '—', ' ') FROM matrix_swfl_res WHERE Matrix_Unique_Id = '23069638' " – Rocco The Taco Nov 07 '14 at 20:04
  • Right on, glad to hear it you found the error Rocco. – Funk Forty Niner Nov 07 '14 at 20:12

1 Answers1

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

kums
  • 2,661
  • 2
  • 13
  • 16