How can export mysql data in to xml using php

Question

Below code is for export data from mysql table as xml file. I have tried several code but not getting the result. Please check and help me.

Currently getting result is

8sarathsarathernakulam423432washington9rahulrahulernakulam21212121newyork10aaaa3london11bbbb1newyork12cccc2washington13dddd3london

Code

<?php
require_once "classes/dbconnection-class.php";
if(isset($_POST['export'])){
    header('Content-type: text/xml');
    $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $root_element = "addressbook"; //fruits
    $xml         .= "<$root_element>";
    $query        = "SELECT AB.id, AB.name, AB.firstname, AB.street, AB.zipcode, AB.city_id, CI.city FROM address_book AS AB INNER JOIN city AS CI ON AB.city_id = CI.id";
    $result      = $mysqli->query($query);
    if (!$result) {
        die('Invalid query: ' . $mysqli->error());
    }

    while($result_array = $result->fetch_assoc()){
        $xml .= "<address>";
        foreach($result_array as $key => $value)
        {
            //$key holds the table column name
            $xml .= "<$key>";

            //embed the SQL data in a CDATA element to avoid XML entity issues
            $xml .= "<![CDATA[$value]]>";

            //and close the element
            $xml .= "</$key>";
        }

        $xml.="</address>";
    }
    $xml .= "</$root_element>";
    header ("Content-Type:text/xml");
    //header('Content-Disposition: attachment; filename="downloaded.xml"');
    echo $xml;
}
?>

Browser shows

<?xml version="1.0" encoding="UTF-8"?><addressbook><address><id><![CDATA[8]]></id><name><![CDATA[sarath]]></name><firstname><![CDATA[sarath]]></firstname><street><![CDATA[ernakulam]]></street><zipcode><![CDATA[42343]]></zipcode><city_id><![CDATA[2]]></city_id><city><![CDATA[washington]]></city></address><address><id><![CDATA[9]]></id><name><![CDATA[rahul]]></name><firstname><![CDATA[rahul]]></firstname><street><![CDATA[ernakulam]]></street><zipcode><![CDATA[2121212]]></zipcode><city_id><![CDATA[1]]></city_id><city><![CDATA[newyork]]></city></address><address><id><![CDATA[10]]></id><name><![CDATA[a]]></name><firstname><![CDATA[a]]></firstname><street><![CDATA[a]]></street><zipcode><![CDATA[a]]></zipcode><city_id><![CDATA[3]]></city_id><city><![CDATA[london]]></city></address><address><id><![CDATA[11]]></id><name><![CDATA[b]]></name><firstname><![CDATA[b]]></firstname><street><![CDATA[b]]></street><zipcode><![CDATA[b]]></zipcode><city_id><![CDATA[1]]></city_id><city><![CDATA[newyork]]></city></address><address><id><![CDATA[12]]></id><name><![CDATA[c]]></name><firstname><![CDATA[c]]></firstname><street><![CDATA[c]]></street><zipcode><![CDATA[c]]></zipcode><city_id><![CDATA[2]]></city_id><city><![CDATA[washington]]></city></address><address><id><![CDATA[13]]></id><name><![CDATA[d]]></name><firstname><![CDATA[d]]></firstname><street><![CDATA[d]]></street><zipcode><![CDATA[d]]></zipcode><city_id><![CDATA[3]]></city_id><city><![CDATA[london]]></city></address></addressbook>

Answer 1

When we are dealing with XML and HTML, the best way to act is ever through a parser. In this particular situation, operating with a parser guarantees a valid XML and a clean, short code.

After defining mySQL query, we init a new DOMDocument with version and encoding, then we set his ->formatOutput to True to print out XML in indented format:

$query = "SELECT AB.id, AB.name, AB.firstname, AB.street, AB.zipcode, AB.city_id, CI.city FROM address_book AS AB INNER JOIN city AS CI ON AB.city_id = CI.id";

$dom   = new DOMDocument( '1.0', 'utf-8' );
$dom   ->formatOutput = True;

Then, we create the root node and we append it to DOMDocument:

$root  = $dom->createElement( 'addressbook' );
$dom   ->appendChild( $root );

At this point, after executing mySQL query, we perform a while loop through each resulting row; for each row, we create an empty node <address>, then we perform a foreach loop through each row's field. For each field, we create an empty childnode with tag as field key, then we append to childnode the field value as CDATA and the same childnode to <address> node; at the end of each while loop, each <address> node is appended to root node:

$result     = $mysqli->query( $query );
while( $row = $result->fetch_assoc() )
{
    $node = $dom->createElement( 'address' );
    foreach( $row as $key => $val )
    {
        $child = $dom->createElement( $key );
        $child ->appendChild( $dom->createCDATASection( $val) );
        $node  ->appendChild( $child );
    }
    $root->appendChild( $node );
}

Now, your XML is ready.

If you want save it to a file, you can do it by:

$dom->save( '/Your/File/Path.xml' );

Otherwise, if you prefer send it as XML you have to use this code:

header( 'Content-type: text/xml' );
echo $dom->saveXML();
exit;

If you want instead output it in HTML page, you can write this code:

echo '<pre>';
echo htmlentities( $dom->saveXML() );
echo '</pre>';

---

• See more about DOMDocument

Answer 2

Go to your phpmyadmin database export and select xml in file format.