从数据库生成XML时，PHP编码错误 [英] PHP Encoding Error when producing XML from database
问题描述
我试图从SQL数据库中获取一个简单的PHP服务来为iPhone应用程序生成XML.该数据库具有多种文本源和字符，其中一些似乎会脱离脚本并产生以下编码错误:
I'm trying to get a simple PHP service to produce XML for an iPhone application, sourcing from an SQL database. The database has a variety of text sources and characters, some of which seem to be throwing off the script and producing the following encoding error:
第1行第6232列上的错误:编码错误
error on line 1 at column 6232: Encoding error
php如下:
<?php
//database configuration
$config['mysql_host'] = "XXX.XXX.XXX.XXX";
$config['mysql_user'] = "XXXX";
$config['mysql_pass'] = "XXXX";
$config['db_name'] = "XXXX";
$config['table_name'] = "articles";
//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";
$xml .= "<$root_element>";
$sql = "SELECT * FROM ".$config['table_name'];
$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
foreach($result_array as $key => $value)
{
$xml .= "<$key>";
$xml .= "<![CDATA[$value]]>";
//and close the element
$xml .= "</$key>";
}
$xml.="</".$config['table_name'].">";
}
}
$xml .= "</$root_element>";
//send xml
header ("Content-Type:text/xml");
echo $xml;
?>
有些文章会导致整个页面失败，有些则不会.任何帮助将不胜感激.
Some articles cause the entire page to fail, some don't. Any help would be greatly appreciated.
推荐答案
您的问题确实很广泛，我能提供的最佳答案是，您应该使用一些现有的库对XML进行编码，而不是编写自己的库(如您所愿).显然失败了，因此XML使用者报告了XML编码错误.
Your question is really broad, the best answer I can give is that you should use some existing library to encode the XML instead of writing your own one (as you obviously fail with the job hence the XML encoding error reported by the XML consument).
使用现有库也可以让您更早地指出问题.例如.对于以下代码，请确保从数据库中获得的所有内容都是UTF-8编码的字符串.
Using an existing library would also allow you to point out problems earlier. E.g. for the following code ensure all you get back from your database are UTF-8 encoded strings.
使用更现代的数据库客户端类也将极大地帮助您写下代码.这是带有 PDO 和 DOMDocument :
Also using a more modern database client class will also help you to greatly just write down the code. Here is an example with PDO and DOMDocument:
### configuration values
$config = array(
'Database' => array(
'dsn' => 'mysql:dbname=test;host=localhost;charset=utf8',
'user' => 'testuser',
'pass' => 'test',
),
'table_name' => 'config',
'table_fields' => '*',
);
### implement database access
class Database extends PDO
{
public function __construct(array $config = null)
{
$config = $config ? : $GLOBALS['config'][__CLASS__];
parent::__construct($config['dsn'], $config['user'], $config['pass']);
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
}
}
### setup the datasource ($rows)
$db = new Database();
$rows = $db->query("SELECT $config[table_fields] FROM $config[table_name]");
### setup the XML encoder ($doc)
$doc = new DOMDocument();
$doc->formatOutput = true;
$doc->loadXML("<$config[table_name]s/>");
$doc->encoding = 'utf-8';
### fetch data from the datasource and encode the XML
foreach ($rows as $row) {
$child = $doc->createElement($config['table_name']);
$child = $doc->documentElement->appendChild($child);
foreach ($row as $key => $value) {
$child->appendChild($doc->createElement($key, $value));
}
}
### output XML
header("Content-Type:text/xml");
echo $doc->saveXML();
看到DomDocument在这里要正确地对从数据库返回的UTF-8字符串进行编码. (通常)这里不再需要<![CDATA[...]]>.如您所料，您可能会在其中放置一些破坏XML编码的内容.
See that DomDocument is taking care here of properly encoding the UTF-8 strings that are returned from the database. There is (normally) no need for <![CDATA[...]]> here any longer. As you can imagine you likely put something in there that broke your XML encoding.
另外，对于数据库交互而言，您的大多数代码也不是必需的，您可以仅对行进行迭代，如果没有行，则不会进行迭代.通常最好用Iterator来表达，foreach语言构造可以在现代数据库接口提供的语言上进行操作.从技术上讲，您可以在这里用许多其他东西替换$rows，例如迭代器一个接一个地遍历多个表.
Also for the database interaction, most of your code is not necessary either, you can just iterate over the rows, if there ain't no rows, there would be no iteration. This is normally best expressed with an Iterator the foreach language construct can operate on which is provided by modern database interfaces. Technically you can replace $rows here with many other things, like an iterator that goes over multiple tables one after the other.
另外使用异常错误模式可以使您免去在代码库中进行检查和die的麻烦.
Additionally using the exception error mode does spare you to place checks and dies all over your codebase.
示例输出是:
<?xml version="1.0" encoding="utf-8"?>
<configs>
<config>
<id>1</id>
<option>value for option with ID1</option>
</config>
<config>
<id>2</id>
<option>value for option with ID2</option>
</config>
...
</configs>
如果您仍然需要创建CDATA元素，则它的工作原理类似(我仅在此处显示脚本的一部分，该脚本仅包含添加CDATA部分而不是子值的微小修改):
If you then still need to create CDATA elements, it works similarly (I show only a part of the script here, which only contains a slight modification adding CDATA sections instead of a child value):
### fetch data from the datasource and encode the XML
foreach ($rows as $row) {
$child = $doc->createElement($config['table_name']);
$child = $doc->documentElement->appendChild($child);
foreach ($row as $key => $value) {
$child->appendChild($doc->createElement($key))
->appendChild($doc->createCDATASection($value))
;
}
}
在这里，DOMDocument也负责正确编码CDATA节.您可能没有做过的事.
Here as well, DOMDocument takes care of properly encoding the CDATA section. Something likely you didn't do.
Potential problems you still might run into are with table or row names that are invalid XML names. But then DOMDocument will actually tell you so you know while generating the XML, not only afterwards with an encoding error.
这篇关于从数据库生成XML时，PHP编码错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
