php - 为父子表生成 XML 数据

Question

我正在尝试从父子表生成 XML。这是我要实现的 XML 格式。 PHP 代码仅适用于一张表。我不确定如何从子表生成 XML。感谢您的帮助。

  <SalesOrder>
    <Sales>
     <SalesOrderNumber>SO43660</SalesOrderNumber>
     <PurchaseOrderNumber>PO18850127500</PurchaseOrderNumber>
     <AccountNumber>3333</AccountNumber>
     <OrderDate>2001-07-01</OrderDate>
     <Details>
       <SalesOrderDetailID>13</SalesOrderDetailID>
       <Name>Road-650 Red, 44</Name>
       <CarrierTrackingNumber>6431-4D57-83</CarrierTrackingNumber>
     </Details>
     <Details>
      <SalesOrderDetailID>14</SalesOrderDetailID>
      <Name>Road-450 Red, 52</Name>
      <CarrierTrackingNumber>6431-4D57-83</CarrierTrackingNumber>
    </Details>
   </Sales>
  </SalesOrder>

这是 PHP 代码。

$result = mysql_query("SELECT * from table1");

header("Content-Type: application/xml");
echo sqlToXml($result, "SalesOrder", "sales");

function sqlToXml($queryResult, $rootElementName, $childElementName)
{

$xmlData = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n"; 
$xmlData .= "<" . $rootElementName . ">";

while($record = mysql_fetch_object($queryResult))
{ 
    $xmlData .= "<" . $childElementName . ">";
    for ($i = 0; $i < mysql_num_fields($queryResult); $i++)
    { 
        $fieldName = mysql_field_name($queryResult, $i); 
        $xmlData .= "<" . $fieldName . ">";
        if(!empty($record->$fieldName))
            $xmlData .= $record->$fieldName; 
        else
            $xmlData .= "null"; 

        $xmlData .= "</" . $fieldName . ">"; 
    } 
    $xmlData .= "</" . $childElementName . ">"; 
} 
$xmlData .= "</" . $rootElementName . ">"; 
}

这是架构。

SalesOrder Table                              SalesOrderDetail
----------------                              ------------------
SalesOrderNumber Varchar(20)                  SalesOrderDetailID    INT
PurchaseOrderNumber Varchar(20)               SalesOrderNumber      Varchar(20)
AccountNumber       Varchar(20)               Name                  Varchar(30)    
OrderDate           Date                      CarrierTrackingNumber  Varchar(30)

Answer 1

这两行之间:

} 
$xmlData .= "</" . $childElementName . ">"; 

添加以下内容:

$detail = mysql_query("SELECT * from table2 WHERE salesordernumber = '".$record->SalesOrderNumber."'");
while ($row = mysql_fetch_object($detail)) { 
   $xmlData .= "<Details>";
   $xmlData .= "<SalesOrderDetailID>" . $row->SalesOrderDetailID . "</SalesOrderDetailID>";
   $xmlData .= "<Name>" . $row->Name . "</Name>";
   $xmlData .= "<CarrierTrackingNumber>" . $row->CarrierTrackingNumber . "</CarrierTrackingNumber>";
   $xmlData .= "</Details>";
}

第一行运行查询以从第二个表中获取数据第二行是对返回记录的循环——使 $row 成为每条记录的对象。 while 循环中的所有内容都添加到 XML 字符串 ($xmlData)

链接到 mysql_fetch_object文档