Converting MySQL query to XML

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phodges
Forum Newbie
Posts: 2
Joined: Sat May 09, 2009 11:20 am

Converting MySQL query to XML

Post by phodges »

Hi, bit of a newbie to PHP and cannot work out why this does not work.

I can get it to create the XML as long as I do not have the closing tag in this line:

Code: Select all

$xml_output .= "\t\t<" . $colName . ">" . $nodeData . "</" . $colName . ">\n";
As soon as I add the closing tag it doesn't work.

Any help would be very much appreciated.

Code: Select all

$query = "SELECT * FROM listing";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
 
$colLength = mysql_num_fields($resultID);
 
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<data>\n";
 
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
 
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "\t<listings>\n";
        
        for($y = 0 ; $y < $colLength ; $y++){
        
            $colName = mysql_field_name($resultID, $y);
            
            $nodeData = $row[$colName];
            
            $nodeData = str_replace("&", "&", $nodeData);
            $nodeData = str_replace("<", "<", $nodeData);
            $nodeData = str_replace(">", ">", $nodeData);
            $nodeData = str_replace("\"", """, $nodeData);
    
            $xml_output .= "\t\t<" . $colName . ">" . $nodeData . "</" . $colName . ">\n";  
        }
    $xml_output .= "\t</listings>\n";
}
 
$xml_output .= "</data>";
echo $xml_output;
 
?>
 
Last edited by Benjamin on Sun May 10, 2009 12:35 pm, edited 1 time in total.
Reason: Added [code=php] tags.
david64
Forum Commoner
Posts: 53
Joined: Sat May 02, 2009 8:12 am
Location: Wales

Re: Converting MySQL query to XML

Post by david64 »

I have a function that utilises PHP DOM (http://uk.php.net/book.dom) that you can use to do this automatically.

If you download the package here: http://semlabs.co.uk/products/xfl

There is an XML class in the root directory in the file XML.php

You can convert an array (such as one from a DB query) to XML by doing the following:

Code: Select all

<?php
require_once( 'XML.php' );
$xml = new XML();
$xml->createDocument( 'utf-8', 'no', false, 'root' );
$xml->arrayToXMLVal( $xml->documentElement, $arr );
echo $xml->saveXML();
?>
Last edited by Benjamin on Sun May 10, 2009 12:35 pm, edited 1 time in total.
Reason: Changed code type from text to php.
phodges
Forum Newbie
Posts: 2
Joined: Sat May 09, 2009 11:20 am

Re: Converting MySQL query to XML

Post by phodges »

Cheers.

I gave this a try, but it didn't seem to do anything. Just displays a blank page, no errors.

Code: Select all

 
<?php
 
header("Content-type: text/xml");
 
$host = "localhost";
$user = "root";
$pass = "root";
$database = "dddd";
 
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
 
$query = "SELECT * FROM listing";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
    
require_once( 'XML.php' );
$xml = new XML();
$xml->createDocument( 'utf-8', 'no', false, 'root' );
$xml->arrayToXMLVal( $xml->documentElement, $resultID );
echo $xml->saveXML();
 
?>
 
Last edited by Benjamin on Sun May 10, 2009 12:36 pm, edited 1 time in total.
Reason: Added [code=php] tags.
david64
Forum Commoner
Posts: 53
Joined: Sat May 02, 2009 8:12 am
Location: Wales

Re: Converting MySQL query to XML

Post by david64 »

What happends when you print_r() the $resultID variable. It's been a while since I used those mysql functions. Also noticed an error in the code I sent you. This works:

Code: Select all

<?php
 
$arr = array( 'this' => 'that' );
 
require_once( 'XML.php' );
$xml = new XML();
$xml->createDocument( 'utf-8', 'no', false, 'root' );
$xml->arrayToXMLVal( $xml->doc->documentElement, $arr );
echo $xml->saveXML();
?>
This prints out an XML document. If you don't want the XML decleration do this:

Code: Select all

<?php
 
$arr = array( 'this' => 'that' );
 
require_once( 'XML.php' );
$xml = new XML();
$xml->createDocument( 'utf-8', 'no', false, 'root' );
$xml->arrayToXMLVal( $xml->doc->documentElement, $arr );
echo $xml->get( '//root' )->C14N( true );
?>

Change the name of "root" if you want a different root element for the document. You can only have one element as the root of the document, like <html> in HTML.
Last edited by Benjamin on Sun May 10, 2009 12:36 pm, edited 1 time in total.
Reason: Changed code type from text to php.
david64
Forum Commoner
Posts: 53
Joined: Sat May 02, 2009 8:12 am
Location: Wales

Re: Converting MySQL query to XML

Post by david64 »

This class is a wrapper for the DOM classes in PHP: http://uk.php.net/manual/en/book.dom.php

You can find out more info in the comments inside it.
Post Reply