Incorrect XML format output

Question

Below mentioned code is able to fetch data from the database and output is also in XML format but the order in which I want is not correct.

<?php
    $config['mysql_host'] = "localhost";
    $config['mysql_user'] = "market";
    $config['mysql_pass'] = "12345";
    $config['db_name']    = "marketing";
    $config['table_name'] = "data";
    $root = customers; 

    //connect to host
    mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
    //select database
    @mysql_select_db($config['db_name']) or die( "Unable to select database");

    $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $root_element = $root; 
    $xml         .= "<$root_element>";

    //select all items in table
    $sql = "SELECT * FROM ".$config['table_name'];

    $result = mysql_query($sql);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }

    if(mysql_num_rows($result)>0)
    {
       while($result_array = mysql_fetch_assoc($result))
       {
          $xml .= "<".$config['table_name'].">";

          //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.=$value;

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

          $xml.="</".$config['table_name'].">";
       }
    }

    //close the root element
    $xml .= "</$root_element>";

    //send the xml header to the browser
    header ("Content-Type:text/xml");

    //output the XML data
    echo $xml;
    ?>

Current output :

<customers>
<data>
<customer>111</customer>
<openingbal>111</openingbal>
<pandl>11</pandl>
<currentbal>111</currentbal>
<time>2015-08-28 04:07:21</time>
</data>
<data>
<customer>111</customer>
<openingbal>111</openingbal>
<pandl>11</pandl>
<currentbal>111</currentbal>
<time>2015-08-28 04:07:21</time>
</data>
</customers>

But I want below output in which id = "something"> but above code is not producing the same result.

<Customers>
  <Customer ID ="111">
    <openingbal>111</openingbal>
    <pandl>11</pandl>
    <currentbal>111</currentbal>
    <time>2015-08-14 03:14:30</time>
  </Customer>
  <Customer Id = "111">
    <openingbal>111</openingbal>
    <pandl>11</pandl>
    <currentbal>111</currentbal>
    <time>2015-08-14 03:14:30</time>
  </Customer>
</Customers>

Try simpleXml: http://stackoverflow.com/questions/143122/using-simplexml-to-create-an-xml-object-from-scratch — sanderbee, Aug 28 '15 at 09:38
hi sanderbee . thanks for response. i am new to xml . can u help where to change in code. but i am not able to relate with the link u sent. — Nakul Gupta, Aug 28 '15 at 12:50

sanderbee · Answer 1 · 2015-08-28T13:23:13.197

This will doe the trick.

<?php
    $config['mysql_host'] = "localhost";
    $config['mysql_user'] = "market";
    $config['mysql_pass'] = "12345";
    $config['db_name']    = "marketing";
    $config['table_name'] = "data";
    $config['main_key'] = "customer";
    $root = 'Customers'; 

    //connect to host
    mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
    //select database
    @mysql_select_db($config['db_name']) or die( "Unable to select database");

    $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $root_element = $root; 
    $xml         .= "<$root_element>";

    //select all items in table
    $sql = "SELECT * FROM ".$config['table_name'];

    $result = mysql_query($sql);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }

    if(mysql_num_rows($result)>0)
    {
       while($result_array = mysql_fetch_assoc($result))
       {
          $xml .= "<".ucfirst($config['main_key']);

          //loop through each key,value pair in row
          foreach($result_array as $key => $value)
          {

             if ($key == $config['main_key'])  {
                  $xml .= " ID = \"".$value."\">";
             }else    {
                 //$key holds the table column name
                 $xml .= "<$key>";

                 //embed the SQL data in a CDATA element to avoid XML entity issues
                 $xml.=$value;

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

          $xml.="</".ucfirst($config['main_key']).">";
       }
    }

    //close the root element
    $xml .= "</$root_element>";

    //send the xml header to the browser
    header ("Content-Type:text/xml");

    //output the XML data
    echo $xml;
    ?>

phpmyadmin gave the error. it did not print xml but threw error. — Nakul Gupta, Aug 28 '15 at 13:18
nops . still not working . in IE page is not opening but in google chrome error is "error on line 1 at column 57: error parsing attribute name" — Nakul Gupta, Aug 28 '15 at 13:28
Can you show the generated XML in some way? I've tried the code and it works as expected in my case. — sanderbee, Aug 28 '15 at 13:31
hey sanderbee. Yes its working now. there was error from my side towards the end. I did not close. thanks a lot . :) — Nakul Gupta, Aug 28 '15 at 13:40

Incorrect XML format output

1 Answers1