php and mysql to xml

Question

i have 2 tables, first one which stores restaurant info and second stores dishes served in each. They are linked using res_id.

1) info_main [id, res_id, res_name,res_pc] 2) dishes [id,dishName,price,res_id(Foreign key)]

My SQL query is

$query = "SELECT *  FROM info_main LEFT JOIN dishes ON info_main.res_id = dishes.res_id";

I am inserting the results from the query into an xml file which works fine. Below is the code:

    $query = "SELECT *  FROM info_main LEFT JOIN dishes ON info_main.res_id = dishes.res_id";
$result = mysql_query($query);

if (!$result) {
  die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");

echo '<markers>';

// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  echo '<marker> ';

      echo '<detail1>';
        echo '<resdetails ';
            echo 'name="' . parseToXML($row['res_name']) . '" ';
            echo 'id="' . parseToXML($row['res_ID']) . '" ';
            echo 'pc="' . parseToXML($row['res_pc'] ). '" ';
        echo '/>';

        echo '<dishdetails ';
            echo 'name="' . parseToXML($row['dishName']) . '" ';
            echo 'price="' . parseToXML($row['price']) . '" ';
        echo '/>';

      echo '</detail1>';

      echo '</marker>';
    }

This work fine however if a restaurant has 3 dishes in the database, then xml create 3 nodes: Something like this:

xml file putput

I want the xml structure like

<detail1>
<resdetails name="spoted dog" id="xyz" pc="xyz"/>
<dishdetails name="bean burger" price="1" />
<dishdetails name="cheese and tomato panini" price="3" />
<dishdetails name="veg salad" price="2" />
</details1>

I cant figure out a way how to achieve the above stated xml structure. Your help is greatly appreciated. Thanks

so... do you just want all the rows part of a single <detail>? — gloomy.penguin
– gloomy.penguin, Commented Nov 14, 2013 at 16:09
you need to loop once to create your key data ie resdetails and then loop again to bring the dishes out. or do a check for the last res name used so it doesn't duplicate it — Dave
– Dave, Commented Nov 14, 2013 at 16:09

Dave · Accepted Answer · 2013-11-14 16:13:02Z

 $query = "SELECT *  FROM info_main LEFT JOIN dishes ON info_main.res_id = dishes.res_id";
$result = mysql_query($query);

if (!$result) {
  die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");

echo '<markers>';

// Iterate through the rows, printing XML nodes for each
$resname = "";
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
   if ($resname!=$row['res_name']) {
   //restname isn't populated or doesn't match current so output new headers
     echo '<marker> ';
      echo '<detail1>';
        echo '<resdetails ';
            echo 'name="' . parseToXML($row['res_name']) . '" ';
            echo 'id="' . parseToXML($row['res_ID']) . '" ';
            echo 'pc="' . parseToXML($row['res_pc'] ). '" ';
        echo '/>';
   }
        //this bit needs to always happen
        echo '<dishdetails ';
            echo 'name="' . parseToXML($row['dishName']) . '" ';
            echo 'price="' . parseToXML($row['price']) . '" ';
        echo '/>';


   if ($resname!=$row['res_name']) {
   //restname isn't populated or doesn't match current so output new headers
      echo '</detail1>';

      echo '</marker>';
   }
   $resname = $row['res_name'];  //set resname to this res_name as this is our check to see if we've already put out required headers for this item that way every change it'll put this back in
    }

Something like this (note may need some tidying up)

i tried this code, it gave me an error of too much info at the end of the docuemnt. and when i saw the xml build. The structure was like <marker><detail1><resdetails name="xyz"><dishdetails name="xyz" price="xyz"/></detail1> <dishdetails name="xyz" price="xyz"/> <dishdetails name="xyz" price="xyz"/> which is not what i want
too much info at end of document is an unclosed tag or a carriage return at end of document also it should have put the dish details inside of the detail1 tag and looks like the resdetails tag isn't closed needs a / as I said its close not 100% though needs some tidying up but its 90% there
sorry @Dave, you are right, markers tag was unclosed at the end, my fault, however the structure is same as mentioned as above :(. you can have a look at this: talentedash.co.uk/veggps/php_to_xml.php
check your data set in the db the xml is perfectly formed but it looks like there aren't multiple dish's in your db try running query directly against the table ?

Edward Charkow · Accepted Answer · 2013-11-14 16:13:52Z

0

For that structure just do this:

  echo '<marker> ';

  echo '<detail1>';

 while ($row = @mysql_fetch_assoc($result)){

 echo '<dishdetails ';
        echo 'name="' . parseToXML($row['dishName']) . '" ';
        echo 'price="' . parseToXML($row['price']) . '" ';
    echo '/>';

 }

echo '</detail1>';

  echo '</marker>';

All that is really different, is that I moved a portion of your code out of the while loop.

answered Nov 14, 2013 at 16:13

Edward Charkow

772 bronze badges

2 Comments

Dave Over a year ago

but that won't work then for multiple restraunts as he's using join so he's going to return the same res details for every dish added so he just needs to only put out the detail1 tag on the first iteration of a new res_name the rest stays the same.

Edward Charkow Over a year ago

Your right, didn't think that through, just looked at "what he wanted". Dave's answer is 100 percent correct above.

Ash · Accepted Answer · 2013-11-16 23:50:27Z

Atlast i got that working, i used two query's, one getting distinct restarant and other getting dishname. i used loop in the main loop. below is the code.

$query = "SELECT DISTINCT  *  FROM info_main";

$result = mysql_query($query);


if (!$result) {
  die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");

echo '<markers>';

// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  $id=$row['res_id'];
  // ADD TO XML DOCUMENT NODE
  echo '<marker> ';

      echo '<detail1>';
        echo '<resdetails ';
            echo 'name="' . parseToXML($row['res_name']) . '" ';
            echo 'id="' . parseToXML($id) . '" ';
            echo 'pc="' . parseToXML($row['res_pc'] ). '" ';
        echo '/>';

        $dishes = "SELECT * FROM dishes WHERE res_id = '" . $id . "'";

        $dish = mysql_query($dishes);

        while ($row2 = @mysql_fetch_assoc($dish)){
          $id2 = $row2['res_id'];

            echo '<dishdetails ';
                echo 'name="' . parseToXML($row2['dishName']) . '" ';
                echo 'price="' . parseToXML($row2['price']) . '" ';
            echo '/>';

        }

      echo '</detail1>';

      echo '</marker>';
    }
echo '</markers>';

Collectives™ on Stack Overflow

php and mysql to xml

3 Answers 3

4 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Related