Converting MYSQL table data directly to an XML in PHP

Question

I want to now if there is some inbuilt function that will create an XML directly from MYSQL result-set after executing SELECT query ?

Answer 1

I just wrote this and then thought id search to see if anyone else had written it. It looks simpler than the accepted answers tutorials. My $results comes from $result = mysql_query($query,$link);

$xmlDom = new DOMDocument();
$xmlDom->appendChild($xmlDom->createElement('results'));
$xmlRoot = $xmlDom->documentElement;

while ( $row = mysql_fetch_row($result) ) 
    {
      $xmlRowElementNode = $xmlDom->createElement('row');

      $i=0;
      for($i=0;$i<mysql_num_fields($result);$i++)
      {
          $xmlRowElement = $xmlDom->createElement(mysql_field_name($result,$i));
          $xmlText = $xmlDom->createTextNode($row[$i]);
            $xmlRowElement->appendChild($xmlText);

            $xmlRowElementNode->appendChild($xmlRowElement);
      }

      $xmlRoot->appendChild($xmlRowElementNode);
   }


    header('Content-type:  text/xml');
    echo $xmlDom->saveXML();

This will procude XML in the form of

<results>
    <row1>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row1>
    <row2>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row2>
    <row3...>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row3...>
</results>

For any SELECT query.

Answer 2

There is no inbuilt function that will create XML directly from MYSQL result-set after executing SELECT query.

You have to write code for this

Some nice tutorials are this..

http://www.codediesel.com/php/converting-mysql-queries-to-xml/

http://www.mightywebdeveloper.com/coding/mysql-to-xml-php/

Answer 3

Generally your MySQL result will be returned as an array or an object, which you can then convert to XML or another format. You can use SimpleXML, which has been explained here: How to convert array to SimpleXML