Using PHP DOM to create XML files from MySQL data

Question

I have created a MySQL table and would like to save the contents of the table in an XML file for use by other applications. I can access the data fine and echo the data on a broswer window, but on saving using the DomDocument::save('thexmlfile.xml'), I cannot see any new files created in the directory location of the running PHP file.

web-nomad · Accepted Answer · 2012-04-04T11:32:35.850

0

you will have to create the dom xml from the mysql data and then save it in an xml file.An example:

$sql = 'select * from messages';
$run = mysql_query($sql, $link);

if( $run && mysql_num_rows( $run ) ) {
    $doc = new DOMDocument( '1.0' );
    $doc->formatOutput = true;
    $doc->preserveWhiteSpace = true;

    $root = $doc->createElement( 'data' );
    $doc->appendChild( $root );

    while( ( $fetch = mysql_fetch_assoc( $run ) )!== false ) {
        $node = $doc->createElement( 'node' );
        $root->appendChild( $node );

        foreach( $fetch as $key => $value ) {
            createNodes( $key, $value, $doc, $node );
        }
    }
    $doc->save("thexmlfile.xml");
}

function createNodes( $key, $value, $doc, $node ) {
    $key = $doc->createElement( $key );
    $node->appendChild( $key );
    $key->appendChild( $doc->createTextNode( $value ) );
}

Now, you should see the xml file.

Hope, it helps.

edited Apr 04 '12 at 11:32

answered Apr 04 '12 at 10:24

web-nomad

6,003
3
34
49

Thank you @Pushpesh. problem is,I am now getting an error "XML Parsing Error: syntax error Location: protocal://localhost/practice/displayQueue.php Line Number 1, Column 1:Data not found." This's also shown when I comment the line $doc->save("thexmlfile.xml") and add echo doc->saveXML(); ^ – Jaseme Jakorango Apr 04 '12 at 10:56
ok, do you have the mysql table structure i gave in my example...or if you have a separate structure, post it here...i will make suitable changes... – web-nomad Apr 04 '12 at 11:01
I created table using CREATE TABLE `messages` ( `id` int(10) unsigned NOT NULL auto_increment, `senderNumber` varchar(50) default NULL, `smsMessage` varchar(1600) default NULL, `sentTime` timestamp NULL default CURRENT_TIMESTAMP, `receivedTime` datetime default NULL, `operator` varchar(50) default NULL, `messageType` varchar(50) default 'SMS:TEXT', PRIMARY KEY (`id`) ) ENGINE=MyISAM; Now on displaying i just want to display 3 fields senderNumber, smsMessage and timeReceived. – Jaseme Jakorango Apr 04 '12 at 11:08

score 0 · Answer 2 · answered Jun 28 '12 at 22:48

Hm, your question is about DOM, the accepted answer is about DOM, but you don't seem to need the capabilities of this, then libxml's brother SimpleXML seems much more straight forward... I assume your problem is long over, but just for completeness sake:

$sql = 'select * from messages';
$run = mysql_query($sql, $link);

if( $run && mysql_num_rows( $run ) ) {
    $xml = new SimpleXMLElement('<data/>');
    while($fetch = mysql_fetch_assoc($run)) {
        $node = $root->addChild('node');
        foreach( $fetch as $key => $value ) {
            $node->addChild($key,$value);
        }
    }
    $xml->asXML("thexmlfile.xml");
}

Using PHP DOM to create XML files from MySQL data

2 Answers2

Linked