php - 将一个父级和多个相同的子级转换为 xml

Question

嗨，我有一种情况，我想使用 php 从 mysql 中的一些表创建 xml，到目前为止我没有问题，但是当我有多个相同的命名子项时，我的问题就出现了。

我的表格如下:

自行车

+-----+-----------+-----------+---------+
| Uid | Bike_name | Bike_year | Bike_id |
+-----+-----------+-----------+---------+
|   1 | CBR600    |      2001 |      53 |
|   2 | ZXR750    |      1995 |      27 |
|   3 | FZR1000   |      1992 |      33 |
+-----+-----------+-----------+---------+

独特的功能

+-----+------------------+---------+
| Uid |     Feature      | Bike_id |
+-----+------------------+---------+
|   1 | Micron exhaust   |      27 |
|   2 | Single seat      |      27 |
|   3 | Scorpion exhaust |      53 |
|   4 | Custom paint     |      53 |
|   5 | L.E.D lights     |      53 |
|   6 | Induction kit    |      53 |
+-----+------------------+---------+

自行车事实

+-----+-----------------+-----------+--------+---------+
| Uid | nought_to_sixty | Top_speed | weight | Bike_id |
+-----+-----------------+-----------+--------+---------+
|   1 |             3.2 |       160 |    120 |      27 |
|   2 |               4 |       160 |    150 |      33 |
|   3 |             3.5 |       150 |    100 |      53 |
+-----+-----------------+-----------+--------+---------+

我想要的输出如下:

<Bikes>
  <Bike>
    <Bike_id>53</Bike_id>
    <Bike_name>CBR600</Bike_name>
    <Bike_year>2001</Bike_year>
    <Bike_facts>
        <nought_to_sixty>3.5</nought_to_sixty>
        <Top_speed>150</Top_speed>
        <Weight>100</Weight>
    </Bike_facts>
    <Unique_features>
        <feature>Scorpion exhaust</feature>
        <feature>Custom paint</feature>
        <feature>L.E.D lights</feature>
        <feature>Induction kit</feature>
    </Unique_features>
  </Bike>
  <Bike>
    <Bike_id>27</Bike_id>
    <Bike_name>ZXR750</Bike_name>
    <Bike_year>1995</Bike_year>
    <Bike_facts>
        <nought_to_sixty>3.2</nought_to_sixty>
        <Top_speed>160</Top_speed>
        <Weight>120</Weight>
    </Bike_facts>
    <Unique_features>
        <feature>Micron exhaust</feature>
        <feature>Single seat</feature>
    </Unique_features>
  </Bike>
  <Bike>
    <Bike_id>33</Bike_id>
    <Bike_name>FZR1000</Bike_name>
    <Bike_year>1992</Bike_year>
    <Bike_facts>
        <nought_to_sixty>4</nought_to_sixty>
        <Top_speed>160</Top_speed>
        <Weight>150</Weight>
    </Bike_facts>
  </Bike>
</Bikes>

我可以生产:

<bikes>
  <bike bike_id="27">
    <bike_name>ZXR750</bike_name>
    <bike_year>1995</bike_year>
    <bike_facts>
      <nought_to_sixty>3.2</nought_to_sixty>
      <top_speed>160</top_speed>
      <weight>120</weight>
    </bike_facts>
  </bike>
  <bike bike_id="33">
    <bike_name>FZR1000</bike_name>
    <bike_year>1992</bike_year>
    <bike_facts>
      <nought_to_sixty>4</nought_to_sixty>
      <top_speed>160</top_speed>
      <weight>150</weight>
    </bike_facts>
  </bike>
  <bike bike_id="53">
    <bike_name>CBR600</bike_name>
    <bike_year>2001</bike_year>
    <bike_facts>
      <nought_to_sixty>3.5</nought_to_sixty>
      <top_speed>150</top_speed>
      <weight>100</weight>
    </bike_facts>
  </bike>
</bikes>

使用此代码:

$mysqli = new mysqli("localhost", "root", "", "Bikes");

if ($mysqli->connect_errno) 
{
  echo "Connect failed ".$mysqli->connect_error;
  exit();
}

$query = "SELECT  
b.bike_name,
b.bike_year,
b.bike_id,
f.nought_to_sixty,
f.top_speed,
f.weight
FROM Bike b
LEFT JOIN Bike_facts f on b.bike_id = f.bike_id";

$bikesArray = array();

if ($result = $mysqli->query($query)) 
{
  while ($row = $result->fetch_assoc()) 
    {
      array_push($bikesArray, $row);
    }
  if(count($bikesArray))
  {
     createXMLfile($bikesArray);
  }
  $result->free();
}
$mysqli->close();


function createXMLfile($bikesArray)
{
  $filePath = 'bike.xml';
  $dom     = new DOMDocument('1.0', 'utf-8'); 
  $root      = $dom->createElement('bikes'); 
  for($i=0; $i<count($bikesArray); $i++)
  {
    $bikeid            =  $bikesArray[$i]['bike_id'];  
    $bike_name         =  $bikesArray[$i]['bike_name']; 
    $bike_year         =  $bikesArray[$i]['bike_year']; 
    $nought_to_sixty   =  $bikesArray[$i]['nought_to_sixty']; 
    $top_speed         =  $bikesArray[$i]['top_speed']; 
    $weight            =  $bikesArray[$i]['weight'];

    $bike             = $dom->createElement('bike');
    $bike->setAttribute('bike_id', $bikeid);
    $bike_name        = $dom->createElement('bike_name', $bike_name); 
    $bike->appendChild($bike_name); 
    $bike_year        = $dom->createElement('bike_year', $bike_year); 
    $bike->appendChild($bike_year); 
    $bike_facts       = $dom->createElement('bike_facts');   
    $nought_to_sixty  = $dom->createElement('nought_to_sixty', 
$nought_to_sixty); 
    $bike_facts->appendChild($nought_to_sixty); 
    $top_speed        = $dom->createElement('top_speed', $top_speed); 
    $bike_facts->appendChild($top_speed); 
    $weight           = $dom->createElement('weight', $weight); 
    $bike_facts->appendChild($weight);
    $bike->appendChild($bike_facts);
    $root->appendChild($bike);
  }
  $dom->appendChild($root); 
  $dom->save($filePath); 
} 

但我不确定如何获取 unique_features 部分，是否可以通过 sql 完成，我所有的尝试都为每个 unique_feature 创建了一个新行，这显然是我不想要的。我能想到的唯一其他方法是一个单独的 xml 并将两者合并，我也不真正想要。

Answer 1

首先，更改您的查询以包含 unique_features 中的数据。

$query = "SELECT  
    b.`bike_name`,
    b.`bike_year`,
    b.`bike_id`,
    f.`nought_to_sixty`,
    f.`top_speed`,
    f.`weight`,
    GROUP_CONCAT(u.`feature`) as `features`
FROM Bike b
LEFT JOIN `Bike_facts` f 
    ON b.`bike_id` = f.`bike_id`
LEFT JOIN `unique_features` u
    ON u.`bike_id` = b.`bike_id`
GROUP BY b.`bike_id`
";

在构建 XML 的循环中，展开列 features(将以逗号分隔)，并将其添加到 XML 中，就像对 bike_facts 所做的那样。

要了解 SQL 查询中的数据是什么样子，请在 phpAdmin(或类似的)中运行它。您将看到一列，其中包含以逗号分隔的功能。如果您的数据可能包含逗号，您可以更改 GROUP_CONCAT 函数中的分隔符。您还可以指定数据的顺序。