export XML from mySQL with PHP
Solution 1
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 PDO
instead of those old and rusty mysql_*-functions
, using prepared statements
, which is a cool feature.
// 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.
Solution 2
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>";
Admin
Updated on July 05, 2022Comments
-
Admin almost 2 years
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:
<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.
$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