CODEWITHSUNDEEP
phpxml
user3026660
user3026660

Reputation: 11

convert tabular data (mysql) to xml using php

I have a My SQL database that has some tables. Now I want to extract the that data from a table and have it in XML format.

<?php
    $username = "root";
    $password = "";
    $hostname = ""; 

    //connection to the database
    $dbhandle = mysqli_connect($hostname, $username, $password,"examples" ); 
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    } else
        echo "Connected to MySQL<br>";

    $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; 
    $root_element = "car"."s"; //fruits 
    $xml         .= "<$root_element>";

    //execute the SQL query and return records
    $result = mysqli_query($dbhandle,"SELECT id, name,year FROM cars");
    if (!$result) {
        printf("Error: %s\n", mysqli_error($dbhandle));
        exit();
    }

    if(mysqli_num_rows($result)>0) {    
        while($result_array = mysqli_fetch_assoc($result)) {
            $xml .= "<car>";   
            //loop through each key,value pair in row    
            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.="</car>";    
        } 
    } 

    $xml .= "</$root_element>";   
    //send the xml header to the browser header
    ("Content-Type:text/xml");   
    //output the XML data 
    echo $xml;

    //fetch tha data from the database 
    /*while ($row = mysqli_fetch_array($result)) {
        echo $row['id']." ".$row['name']." ". $row['year'];
        echo"<br>";
    }*/
    //close the connection
    mysqli_close($dbhandle);

?>

however, I got the following error.

The XML page cannot be displayed  Cannot view XML input using style

sheet. Please correct the error and then click the Refresh button, or try again later.

Invalid at the top level of the document. Error processing resource 'http://.... my%20portable%20files/mysqlxml.php'. ... Connected to MySQL<br><?xml version="1.0" encoding="UTF-8"?><cars><car><id><![CDATA[1]]>...`

could you help me please.

Upvotes: 0

Views: 316

Answers (1)

The Marlboro Man
The Marlboro Man

Reputation: 971

The 

echo "Connected to MySQL";

part might be interfering with the XML output. See, when outputting XML you must output xml and nothing else: no spaces, no text, nothing before the initial XML tag (then your root node, then the rest).

Also, I don't see the headers being correctly output, try:

header("Content-Type:text/xml");

And also add your encoding, if you must.

If all fails, please, post a link so we can see the output and debug the problem.

Upvotes: 1

Related Questions