使用PHP DOM将PostgreSQL查询的结果转换为XML [英] Transforming Results of PostgreSQL Query to XML, using PHP DOM

问题描述

给定SQL作为输入，我必须查询PostgreSQL数据库并将结果作为XML返回。我已经用下面的代码：

 <？php 
 
 $ link =host = localhost dbname = company user = pgsql password = password; 
 $ connect = pg_connect（$ link）; 
 
 $ query =SELECT * FROM customer; 
 $ result = pg_query（$ connect，$ query）; 
 
 $ doc = new DomDocument（1.0）; 
 
 $ root = $ doc-> createElement（'data'）; 
 $ root = $ doc-> appendChild（$ root）; 
 
 while（$ row = pg_fetch_assoc（$ result））{
 $ node = $ doc-> createElement（'collection'）; 
 $ node = $ root-> appendChild（$ node）; 
 
 foreach（$ row as $ fieldname => $ fieldvalue）{
 $ node-> appendChild（$ doc-> createElement（$ fieldname，$ fieldvalue））; 
} 
} 
 
 $ doc-> save（cust.xml）; 
 
？> 
  

最终，我将直接将CSS样式表应用于XML文档。我将为每个'collection'节点指定样式信息。然而，有可能会收集集合，甚至子子集合等等。

（所以：主，细节，细节，子细节等）

问题是，我的代码只会为Master，细节，子细节生成XML。如何修改我的代码，以便生成的XML将始终捕获所有级别的数据？

感谢...

---

是的，我指的是层次关系。例如，使用我当前的代码，我可以看到客户表的所有字段属性。然而，如果一个客户属性是地址，客户有多个地址呢？这将需要显示为...

 < data> 
< collection> 
< fname> Joe< / fname> 
< lname> Smith< / lname> 
< address> 
<地址A> 123 ...< / address A> 
<地址B> 234 ...< / address B> 
< / collection> 
< / data> 
  

我正在尝试修改我的代码，以接听地址，虽然属性，具有子属性...

解决方案

首先，您可以考虑在Postgres本身中使用可用的内置 - 在功能。这样做的两个优点是，您的数据抽象功能保持在一起，并且Postgres比PHP更有效地优化了执行此任务。如果你必须对xml进行任何更高级别的修改，xslt应该做到这一点。

至于你提到的问题，我不知道你是否参考表中具有层次关系的数据？

Given SQL as an input, I have to query a PostgreSQL database and return the results as XML. I have done this with the following code:

<?php

$link = "host=localhost dbname=company user=pgsql password=password";
$connect = pg_connect($link);

$query = "SELECT *  FROM customer";
$result = pg_query($connect, $query);

$doc = new DomDocument("1.0");

$root = $doc->createElement('data');
$root = $doc->appendChild($root);

while($row = pg_fetch_assoc($result)){
    $node = $doc->createElement('collection');
    $node = $root->appendChild($node);

    foreach($row as $fieldname => $fieldvalue){
       $node->appendChild($doc->createElement($fieldname, $fieldvalue));
    }
}

$doc->save("cust.xml");

?>

Eventually, I will be applying a CSS stylesheet directly to the XML document. I will specify style information for each 'collection' node. However, it is possible that there will be sub-collections of collections, and even sub-sub-collections and so on.

(So: Master, detail, sub-detail, sub-sub-detail, etc.)

The problem is, my code will only generate XML for Master, detail, sub-detail. How can I modify my code so that the XML generated will always "capture" all of the levels of the data?

Thanks...

---

Yes, I'm referring to a hierarchical relationship. For example, with my current code, I can see all of the attributes, of fields, of the "Customer" table. However, what if one of the customer attributes were 'Address', and the Customer had multiple addresses? This would need to appear as...

 <data> 
   <collection> 
     <fname>Joe</fname> 
     <lname>Smith</lname> 
     <address> 
       <address A> 123...</address A> 
       <address B> 234...</address B> 
   </collection>
 </data>

I'm trying to modify my code to "pick up" on the fact that address, although an attribute, has sub-attributes...

解决方案

First, you may consider performing your xml mapping within Postgres itself using the available built-in functions. Two benefits of this are that your data abstraction functionality stays together and that Postgres is much better optimized to perform this task efficiently than php is. If you have to do any higher level modification of the xml, xslt should do the trick.

As for the problem you mention, I'm not sure if you're referring to the data in the table having a hierarchical relationship?

这篇关于使用PHP DOM将PostgreSQL查询的结果转换为XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！