CODEWITHSUNDEEP
phpmysqlxml
user2272544
user2272544

Reputation:

export XML from mySQL with PHP

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

Upvotes: 3

Views: 22191

Answers (3)

michi
michi

Reputation: 6625

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.

Upvotes: 4

zavg
zavg

Reputation: 11061

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>";

Upvotes: 2

Michael O&#39;Brien
Michael O&#39;Brien

Reputation: 401

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.

Upvotes: 0

Related Questions