MYSQL Parse to XML code

Question

I have some problem with converting mysql to xml using php due to my lack knowledge in PHP. I got this code from a website located here http://www.mightywebdeveloper.com/coding/mysql-to-xml-php/

<?php
header('Access-Control-Allow-Origin: *');

$oid= $_GET['oid'];

//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "thisisuser";
$config['mysql_pass'] = "thisispass";
$config['db_name']    = "mydb";
$config['table_name'] = "mail";

//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 = $config['table_name']."s"; //fruits
$xml         .= "<$root_element>";

//select all items in table
$sql = "SELECT * FROM mail WHERE oid='".$oid."' ORDER BY id ";

//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;

?>

It work just fine after a few edit and the output goes like this.

<mails>
    <mail>
        <id>101221</id>
        <oid>1</oid>
        <from>Test User</from>
        <content>This is a test mail.</content>
    </mail>
        <mail>
        <id>101222</id>
        <oid>1</oid>
        <from>Test User</from>
        <content>This is a test mail.</content>
    </mail>
</mails>

My problem is Id like to display the array count next to the mail tag similar to something like

<mails>
    <mail  id="1"> <-fetched array number?
        <id>101221</id>
        <oid>1</oid>
        <from>Test User</from>
        <content>This is a test mail.</content>
    </mail>
        <mail  id="2">
        <id>101222</id>
        <oid>1</oid>
        <from>Test User</from>
        <content>This is a test mail.</content>
    </mail>
</mails>

Please help me.

If you can, you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's really not hard. — Jay Blanchard
– Jay Blanchard, Commented Jun 9, 2015 at 19:36
you'd have to detect which "note" you're on in the query results, and generate the html appropriately. e.g. $row = fetch(); if ($row is id) { output mail id=1 } else { build regular node } — Marc B
– Marc B, Commented Jun 9, 2015 at 19:40
create xml via php.net/manual/en/book.simplexml.php or php.net/manual/en/book.dom.php. Thats safe and correct way. — venca
– venca, Commented Jun 9, 2015 at 19:51

winmutt · Accepted Answer · 2015-06-09 19:40:40Z

1

Change this:

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

To this:

$xml .= "<".$config['table_name']." id='".$result_array['id']."'>";

answered Jun 9, 2015 at 19:40

winmutt

4052 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

learningXD Over a year ago

Hey, thank you I got 1 last question instead of output the result array, id like it to be numbered. Like <mail id =1> <id>100</id> how to do that?

learningXD Over a year ago

Something like this? <mail id ="1"> <id>10081818</id> </mail> <mail id ="2"> <id>1011109</id> </mail>

Collectives™ on Stack Overflow

MYSQL Parse to XML code

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related