嗨,我有一种情况,我想使用PHP从MySQL中的某些桌子创建XML,我可以遇到这么远,但是当我有多个命名的孩子时,我的问题就会出现。
我所拥有的桌子如下:
自行车
+-----+-----------+-----------+---------+
| Uid | Bike_name | Bike_year | Bike_id |
+-----+-----------+-----------+---------+
| 1 | CBR600 | 2001 | 53 |
| 2 | ZXR750 | 1995 | 27 |
| 3 | FZR1000 | 1992 | 33 |
+-----+-----------+-----------+---------+
unique_features
+-----+------------------+---------+
| 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 |
+-----+------------------+---------+
bike_facts
+-----+-----------------+-----------+--------+---------+
| 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完成,我所有的尝试都为每个solorice_feature创建了一个新的行,显然我不想要。我唯一能想到的是单独的XML并合并了这两个,我也不想要。
首先,将查询更改为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的循环中,爆炸列功能(它将分开逗号(,然后像为Bike_facts一样将其添加到XML中。
。要了解来自SQL查询的数据的样子,请在phpadmin(或类似(中运行它。您会看到一列,其功能由逗号分隔。如果您的数据可能包含逗号,则可以更改group_concat函数中的界限。您还可以指定数据的排序。