3

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

Improve this question
4
  • 1
    You should consider using DomDocument or something similar to handle the actual XML output. Manipulating strings directly makes your code extremely ugly, and getting a nicely indented XML doc is challenging. Commented Apr 12, 2013 at 1:43
  • i agree, its really hard if i want to stack all the song under album. its easey if i have uniq items in each row.. i will look into how DomDocuments work and see if i can figure it out, thanks for the tip Commented Apr 12, 2013 at 10:10
  • Don't write this your own. It has been done already. See XML_Query2XML it also has end-user documentation: query2xml.sourceforge.net/docs - Your benefit: The example the author gives is a very similar thing: Artists and Albums. query2xml.sourceforge.net/docs/html/XML_Query2XML/… Commented Apr 12, 2013 at 10:28
  • Why do you even bother with formatting an XML file? Consider how much bandwidth you will be saving by solely removing all the newline/tab/space characters. Unless, for some reason, you expect humans to edit the XML file which is usually not what XML is used for. Commented Apr 12, 2013 at 23:54

3 Answers 3

Reset to default
4

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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>";
Improve this answer

1 Comment

I tried this and came to the conclusion that if i use $export inside an if loop, i get an error. I did use something simular before but then the tags vent all wrong. Like this <music> <artist name="$artist1"> <album name="$album1"> <song>$song1</song> </album> </artist> </music> <music><song>$song2</song> </music>
0

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.

Improve this answer

Comments