使用 PHP 从 mySQL 导出 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15961390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
export XML from mySQL with PHP
提问by michi
I have a bit of problem with my code at the moment and I hope you can help me with it.
目前我的代码有点问题，我希望你能帮助我。
First of the tabels
第一个表
SELECT artist, album, song
FROM artist
LEFT JOIN album
on artist.artist_ID = album.artist_ID
LEFT JOIN song
on album.album_ID = song.album_ID
ORDER BY artist.artist, album.album_ID, song.song_ID
Im trying to export it as an XML with help of php so im creating the XML direct in the document so i just can press a link to view and access the xml. But the problem im having is that the song dont stack under album. Instead they do this:
我试图在 php 的帮助下将它导出为 XML，所以我直接在文档中创建 XML，所以我只需按一个链接即可查看和访问 xml。但我遇到的问题是这首歌不在专辑下面。相反，他们这样做：
<music>
<artist name="$artist1">
<album name="$album1">
<song>$song1</song>
</album>
</artist>
</music>
<music>
<artist name="$artist1">
<album name="$album1">
<song>$song2</song>
</album>
</artist>
</music>
I want then to stack like this:
然后我想像这样堆叠：
<music>
<artist name="$artist1">
<album name="$album1">
<song>$song1</song>
<song>$song2</song>
<song>$song3</song>
</album>
</artist>
</music>
This is the PHP-code i use at the moment to export to XML, something dont work here. I have been trying to fix it the last 12 hours without luck.
这是我目前用来导出到 XML 的 PHP 代码，在这里不起作用。在过去的 12 个小时里，我一直在尝试修复它，但没有运气。
$export = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
$export="<myTunes>";
while($row = mysqli_fetch_array($result))
{
$export.="<music>";
$artist=$row["artist"];
$album=$row["album"];
$song=$row["song"];
$export.=" <artist name='$artist'>
<album name='$album'>
<song>$song</song>
</album>
</artist>";
$export.="</music>";
}
$export.="</myTunes>";
file_put_contents("export.xml", $export);
echo "<a href='export.xml' target='_blank'>Export database as XML</a>";
Please help if you can, im starting to loose my mind over here. Best Regards, Chris
如果可以，请提供帮助，我在这里开始失去理智。最好的问候，克里斯
回答by michi
I'll suggest a different approach that will lead to the XML you want.
1. I'll use XMLWriter, which comes with PHP, and
2. rely on querying the database quite often. Going to the database often will put some load onto your db-server, which is bad if you create those XML-files every minute or so, or if many users do this at the same time, it's ok if you do it once in a while :-)
3. I'll access the database using PDOinstead of those old and rusty mysql_*-functions, using prepared statements, which is a cool feature.
我将建议一种不同的方法来生成您想要的 XML。
1. 我将使用XMLWriterPHP 附带的
2. 依赖于经常查询数据库。经常访问数据库会给您的数据库服务器带来一些负载，如果您每分钟左右创建这些 XML 文件，或者如果许多用户同时执行此操作，则这很糟糕，如果您一次执行一次就可以了while :-)
3. 我将访问数据库 usingPDO而不是那些旧的和生锈的mysql_*-functions， using prepared statements，这是一个很酷的功能。
// setting up PDO
$dbLocation = 'mysql:dbname=db001;host=localhost';
$dbUser = 'user';
$dbPass = 'password';
$db = new PDO($dbLocation, $dbUser, $dbPass);
// prepare all queries...
$dbArtists = $db->prepare("SELECT * FROM artist");
$dbAlbums = $db->prepare("SELECT * FROM album WHERE artist_ID=:artist_id");
$dbSongs = $db->prepare("SELECT * FROM song WHERE album_ID=:album_id");
// fetch all artists
$dbArtists->execute();
$artists=$dbArtists->fetchAll(PDO::FETCH_ASSOC);
$x=new XMLWriter();
$x->openMemory();
$x->startDocument('1.0','UTF-8');
$x->startElement('music');
foreach ($artists as $artist) {
$x->startElement('artist');
$x->writeAttribute('name',$artist['artist']);
// fetch all albums of this artist
$dbAlbums->execute(array(':artist_id' => $artist['artist_id']));
$albums = $dbAlbums->fetchAll(PDO::FETCH_ASSOC);
foreach ($albums as $album) {
$x->startElement('album');
$x->writeAttribute('name',$album['album']);
// fetch all songs from this album
$dbSongs->execute(array(':album_id' => $album['album_id']));
$songs = $dbSongs->fetchAll(PDO::FETCH_ASSOC);
foreach ($songs as $song) {
$x->startElement('song');
$x->text($song['song']);
$x->endElement(); // song
} // foreach $songs
$x->endElement(); // album
} // foreach $albums
$x->endElement(); // artist
} // foreach $artists
$x->endElement(); // music
$x->endDocument();
$xml = $x->outputMemory();
// now save $xml to a file etc.
回答by zavg
The code below reflects only the general idea of one of the algorithms and, of course, it should be refactored in production version.
下面的代码仅反映了其中一种算法的总体思路，当然，它应该在生产版本中进行重构。
$export = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
$export .= "<myTunes>";
$export .= "<music>";
$lastArtist = $lastAlbum = null;
while($row = mysqli_fetch_array($result))
{
$artist=$row["artist"];
$album=$row["album"];
$song=$row["song"];
if(($lastArtist == null) and ($lastAlbum == null))
$export.="<artist name='$artist'><album name='$album'>";
else
{
if($artist != $lastArtist)
$export.="</artist><artist name='$artist'>";
if($album != $lastAlbum)
$export.="</album><album name='$album'>";
}
$export.=" <song>$song</song>";
$lastArtist = $artist;
$lastAlbum = $album;
}
$export.="</album></artist></music>";
$export.="</myTunes>";
file_put_contents("export.xml", $export);
echo "<a href='export.xml' target='_blank'>Export database as XML</a>";
回答by Michael O'Brien
The problem seems to be that you're looping over all of your results and writing all of the information out with each iteration.
问题似乎是您正在遍历所有结果并在每次迭代中写出所有信息。
You might consider using two queries. The first to get your artist and album which you can loop over and then a second to get your songs, the results of which could also be looped over inside your outer loop to add the songs as child elements to your album element.
您可以考虑使用两个查询。第一个获取您可以循环的艺术家和专辑，然后第二个获取您的歌曲，其结果也可以在您的外循环内循环，以将歌曲作为子元素添加到您的专辑元素中。
