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>

I have the impression that what you say is the result you get actually is just what is visualized in your browser. Please note that a browser is not capable of visualizing xml as you create it in any meaningful way. Consider taking a look at the source of the document your browser shows or dump your output into a file and look at that. — arkascha
– arkascha, Commented Feb 16, 2016 at 16:19
@arkascha, Thanks, I have updated my browser result with my question. I checked that but I didn't found issue. I don't know why result is not getting properly. — Sarath TS
– Sarath TS, Commented Feb 16, 2016 at 16:30
OK, that looks much better! Now if you also tell us what exactly is wrong with the result you get we would actually be able to help with your question... — arkascha
– arkascha, Commented Feb 16, 2016 at 16:32
Your browser will never show you xml like <?xml version="1.0" encoding="UTF-8"?><addressbook><address>... this, it will show only values inside tags, but if you view source the page your will see your full generated xml — Armen
– Armen, Commented Feb 19, 2016 at 12:11

fusion3k · Accepted Answer · 2016-02-21 23:59:05Z

7

+25

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

answered Feb 21, 2016 at 23:59

fusion3k

11.7k4 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Machavity Over a year ago

Somewhere bobince is smiling at this while riding a pony

Sarath TS Over a year ago

@fusion3k Thank you so much

Sukhwinder Sodhi · Accepted Answer · 2016-02-26 09:27:36Z

3

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

answered Feb 26, 2016 at 9:27

Sukhwinder Sodhi

4751 gold badge4 silver badges19 bronze badges

Comments

Szektor · Accepted Answer · 2016-02-19 20:35:16Z

0

Replace

$xml .= "<![CDATA[$value]]>";

with

$xml .= $value;

answered Feb 19, 2016 at 20:35

Szektor

571 silver badge4 bronze badges

1 Comment

Machavity Over a year ago

You're going to have to elaborate on this to avoid downvotes. The OP said he was using CDATA to avoid breaking XML compatability

Emil Borconi · Accepted Answer · 2016-02-19 22:24:28Z

0

IF you want to have it format it "nicely" in the browser add an:

echo "<pre>";

before the:

echo $xml;

Please note this WILL BREAK the XML file, but it will look good in the browser.... if that is what you are after...

answered Feb 19, 2016 at 22:24

Emil Borconi

3,4972 gold badges29 silver badges42 bronze badges

Comments

Abhiii · Accepted Answer · 2016-02-24 09:27:58Z

0

I would suggest to use libraries like SimpleXMLElement etc. to create XML documents.

$xml = new SimpleXMLElement("<?xml version=\"1.0\" encoding=\"UTF-8\" ?><{$root_element}></{$root_element}>"); 
while($result_array = $result->fetch_assoc()){
    foreach($result_array as $key => $value)
    {
        $address = $xml->addChild("address");
        //embed the SQL data in a CDATA element to avoid XML entity issues
        $addressFields = $address->addChild('"' . $key . '"', "<![CDATA[$value]]>");

       //No need to close the element
    }
}
Header('Content-type: text/xml');
print($xml->asXML());

answered Feb 24, 2016 at 9:27

Abhiii

342 bronze badges

Collectives™ on Stack Overflow

How can export mysql data in to xml using php

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related