0

hay gays i want to retrieve xml data from three table stored in mysql. i used the below code it works fine but first it retrieve one record from first table than iterate to second table and print the whole table and then iterate to third table and print the whole table but i want to print first table along with relevant records in second table (not whole table) then from third table and so on. my code is

$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);

$table_second='instructions';
$query="SELECT instructions.instruction_id,instructions.instruction_text FROM $table_second";
$resinner=mysql_query($query, $conn);


$table_third='ingredients';

$query="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM $table_third";
$resthird=mysql_query($query, $conn);


$doc = new DomDocument('1.0');

$root = $doc->createElement('recipes');
$root = $doc->appendChild($root);




while($row = mysql_fetch_assoc($resouter)){


$outer = $doc->createElement($table_first);
$outer = $root->appendChild($outer);

 foreach ($row as $fieldname => $fieldvalue) {
    $child = $doc->createElement($fieldname);
    $child = $outer->appendChild($child);
    $value = $doc->createTextNode($fieldvalue);
    $value = $child->appendChild($value);
  }// foreach
 //while
$inner = $doc->createElement($table_second);
    $inner = $outer->appendChild($inner);
 while($row = mysql_fetch_assoc($resinner)){
    // add node for each record
    

    $inner1=$doc->createElement('instruction');
    $inner1=$inner->appendChild($inner1);
    // add a child node for each field
    foreach ($row as $fieldname => $fieldvalue) {
        $child = $doc->createElement($fieldname);
        $child = $inner1->appendChild($child);
        $value = $doc->createTextNode($fieldvalue);
        $value = $child->appendChild($value);
    } // foreach
 }// while


 $inner=$doc->createElement($table_third);
    $inner=$outer->appendChild($inner);
    
while($row=mysql_fetch_assoc($resthird)){



     $inner2=$doc->createElement('ingredient');
    $inner2=$inner->appendChild($inner2);

    foreach($row as $fieldname=> $fieldvalue)
    {
        $child=$doc->createElement($fieldname);
        $child=$inner2->appendChild($child);
        $value=$doc->createTextNode($fieldvalue);
        $value=$child->appendChild($value);
    }
}
}

mysql_close($conn);
$xml_string = $doc->saveXML();
echo $xml_string;
2
Contributors
9
Replies
10
Views
6 Years
Discussion Span
Last Post by vibhaJ
1

Firstly there should be relation between these three table recipe, instructions and ingredients.
Here you have selected all records in select query rather you should have select query in ingredients for perticular recipe(e.g. 'where' keyword must be there).
post your table structure to make select queries and new code.

0

the primary key for recipe (rec_id) is same for all three tables e.g it is a foreign key in ingredient table as well as in instruction table.

0

i want xml to be print like this

<?xml version="1.0"?> 
<recipes>
  <recipe>
     <rec_id>20</rec_id>
     <name>Haggis Recipe</name> 
     <overview>http://www.scottishrecipes.co.uk/haggis2.htm</overview>
     <time>2009-11-10 12:03:02</time>
     <image>localhost/cafe/pics/haggis-neeps-tatties.jpg</image>
       
          <instructions>
            <instruction>
              <instruction_id>31</instruction_id> 
              <instruction_text>Clean the stomach bag thoroughly and soak overnight. In the morning turn it inside out.</instruction_text>
            </instruction>
            <instruction>
              <instruction_id>32</instruction_id>
              <instruction_text>Wash the pluck and boil for 1.5 hours, ensuring the windpipe hangs over the pot allowing drainage of the impurities.</instruction_text>
            </instruction>
            <instruction>
              <instruction_id>33</instruction_id>
              <instruction_text>Mince the heart and lungs and grate half the liver.</instruction_text>
            </instruction>
            <instruction>
               <instruction_id>34</instruction_id>
               <instruction_text>Chop up the onions and suet.</instruction_text>
            </instruction>
        </instructions>

        <ingredients>
              <ingredient>
                   <ingredient_id>42</ingredient_id>
                   <ingredient_name>1 sheep stomach bag</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>43</ingredient_id>
                   <ingredient_name>1 sheeps pluck - liver, lungs and heart</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>44</ingredient_id>
                   <ingredient_name>3 onions</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>45</ingredient_id>
                   <ingredient_name>250g beef Suet</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>46</ingredient_id>
                   <ingredient_name>150g oatmeal</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>47</ingredient_id>
                    <ingredient_name>salt and black pepper</ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>48</ingredient_id>
                    <ingredient_name>a pinch of cayenne</ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>49</ingredient_id>
                    <ingredient_name>150mls of stock/gravy </ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
        </ingredients>
      </recipe>

<recipe>
     <rec_id>21</rec_id>
     <name>some name</name> 
     <overview>http://www.scottishrecipes.co.uk/haggis2.htm</overview>
     <time>2009-11-10 12:03:02</time>
     <image>localhost/cafe/pics/haggis-neeps-tatties.jpg</image>
       
          <instructions>
            <instruction>
              <instruction_id>35</instruction_id> 
              <instruction_text>Clean the stomach bag thoroughly and soak overnight. In the morning turn it inside out.</instruction_text>
            </instruction>
            <instruction>
              <instruction_id>36</instruction_id>
              <instruction_text>Wash the pluck and boil for 1.5 hours, ensuring the windpipe hangs over the pot allowing drainage of the impurities.</instruction_text>
            </instruction>
            <instruction>
              <instruction_id>37</instruction_id>
              <instruction_text>Mince the heart and lungs and grate half the liver.</instruction_text>
            </instruction>
            <instruction>
               <instruction_id>38</instruction_id>
               <instruction_text>Chop up the onions and suet.</instruction_text>
            </instruction>
        </instructions>

        <ingredients>
              <ingredient>
                   <ingredient_id>43</ingredient_id>
                   <ingredient_name>1 sheep stomach bag</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>44</ingredient_id>
                   <ingredient_name>1 sheeps pluck - liver, lungs and heart</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>45</ingredient_id>
                   <ingredient_name>3 onions</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>46</ingredient_id>
                   <ingredient_name>250g beef Suet</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                   <ingredient_id>46</ingredient_id>
                   <ingredient_name>150g oatmeal</ingredient_name>
                   <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>48</ingredient_id>
                    <ingredient_name>salt and black pepper</ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>49</ingredient_id>
                    <ingredient_name>a pinch of cayenne</ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
              <ingredient>
                    <ingredient_id>50</ingredient_id>
                    <ingredient_name>150mls of stock/gravy </ingredient_name>
                    <ammount>14</ammount>
              </ingredient>
        </ingredients>
      </recipe>
</recipes>

but my code first print the recipe first record then instruction whole table and then ingredients whole table. Please help me

Edited by zeeshan_kust: n/a

0

Try this code.
Run at ur end and post output.

<?
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);


$doc = new DomDocument('1.0');
$root = $doc->createElement('recipes');
$root = $doc->appendChild($root);

while($row = mysql_fetch_assoc($resouter)){


$outer = $doc->createElement($table_first);
$outer = $root->appendChild($outer);

 foreach ($row as $fieldname => $fieldvalue) {
    $child = $doc->createElement($fieldname);
    $child = $outer->appendChild($child);
    $value = $doc->createTextNode($fieldvalue);
    $value = $child->appendChild($value);
  }// foreach
 //while
 
//---------------------------- fetch instructions ---------------------------
$table_second='instructions';
$query="SELECT instructions.instruction_id,instructions.instruction_text FROM $table_second where rec_id = ".$row['rec_id'];
$resinner=mysql_query($query, $conn);

$inner = $doc->createElement($table_second);
$inner = $outer->appendChild($inner);
while($row = mysql_fetch_assoc($resinner)){
    // add node for each record
    

    $inner1=$doc->createElement('instruction');
    $inner1=$inner->appendChild($inner1);
    // add a child node for each field
    foreach ($row as $fieldname => $fieldvalue) {
        $child = $doc->createElement($fieldname);
        $child = $inner1->appendChild($child);
        $value = $doc->createTextNode($fieldvalue);
        $value = $child->appendChild($value);
    } // foreach
 }// while


//---------------------------- fetch ingredients ---------------------------
$table_third='ingredients';
$query="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM $table_third where rec_id = ".$row['rec_id'];
$resthird=mysql_query($query, $conn);

$inner=$doc->createElement($table_third);
$inner=$outer->appendChild($inner);
    
while($row=mysql_fetch_assoc($resthird)){



     $inner2=$doc->createElement('ingredient');
    $inner2=$inner->appendChild($inner2);

    foreach($row as $fieldname=> $fieldvalue)
    {
        $child=$doc->createElement($fieldname);
        $child=$inner2->appendChild($child);
        $value=$doc->createTextNode($fieldvalue);
        $value=$child->appendChild($value);
    }
}

}

mysql_close($conn);
$xml_string = $doc->saveXML();
echo $xml_string;
?>
0

now it works fine but this gives me the error.
line 80 code is while($row=mysql_fetch_assoc($resthird))

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0095     348736   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0126     348832   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0210     348736   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0290     348696   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0395     348672   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/cafe/view/recipe_xml.php on line 80
 
Call Stack:
    0.0009     343196   1. {main}() /var/www/cafe/view/recipe_xml.php:0
    0.0427     348752   2. mysql_fetch_assoc() /var/www/cafe/view/recipe_xml.php:80
 
<?xml version="1.0"?> 
<recipes><recipe><rec_id>14</rec_id><name>Spaghetti with Crab and Arugula</name><overview>http://www.weloveseafood.com/SpaghettiWithCrabAndArugula.html</overview><time>2009-11-10 11:31:38</time><image>localhost/pics/SpaghettiWithCrabAndArugula.jpg</image><instructions><instruction><instruction_id>14</instruction_id><instruction_text>Cook spaghetti according to directions on package. Drain and set aside.</instruction_text></instruction><instruction><instruction_id>15</instruction_id><instruction_text>In a large saucepan, heat olive oil. Add garlic and chili and saute for 30 seconds. Stir in crabmeat and lemon juice. Season with salt and pepper. Cook for 3-5 minutes, stirring constantly. Add arugula leaves.</instruction_text></instruction><instruction><instruction_id>16</instruction_id><instruction_text>Add pasta and toss to coat. Adjust taste with salt and pepper if necessary. Serve immediately.</instruction_text></instruction></instructions><ingredients/></recipe><recipe><rec_id>15</rec_id><name>stew recipe </name><overview>http://www.scottishrecipes.co.uk/stew.htm</overview><time>2009-11-10 11:40:34</time><image>localhost/pics/stew2.jpg</image><instructions><instruction><instruction_id>17</instruction_id><instruction_text>Coat the steak with the flour, keeping any leftover flour.</instruction_text></instruction><instruction><instruction_id>18</instruction_id><instruction_text>Heat the vegetable oil in a large pot.</instruction_text></instruction><instruction><instruction_id>19</instruction_id><instruction_text>Add the steak and cook until brown then remove the steak and reduce the heat.</instruction_text></instruction><instruction><instruction_id>20</instruction_id><instruction_text>Add the onion and slowly cook until the onion is tender. Add the remaining flour.</instruction_text></instruction><instruction><instruction_id>21</instruction_id><instruction_text>Slowly stir in the stock, add the Worcestershire sauce, salt and pepper and continue to cook, stirring until all the ingredients in the pot are blended together and the mixture gets thicker.</instruction_text></instruction><instruction><instruction_id>22</instruction_id><instruction_text>Put the steak back into the pot, reduce the heat and simmer for about two and a half hours.</instruction_text></instruction><instruction><instruction_id>23</instruction_id><instruction_text>Add the vegetables, stirring well and bring back to the boil. Reduce the heat, cover and simmer for about 30 minutes or until the steak and vegetables are lovely and tender.</instruction_text></instruction></instructions><ingredients/></recipe><recipe><rec_id>18</rec_id><name>Chicken Casserole</name><overview>http://www.scottishrecipes.co.uk/chickencasserole.htm</overview><time>2009-11-10 11:53:24</time><image>localhost/cafe/pics/chicken_casserole.JPG</image><instructions><instruction><instruction_id>24</instruction_id><instruction_text>Melt the butter in a pan and then brown the chicken pieces. Drain off the fat and place the chicken into a casserole dish.</instruction_text></instruction><instruction><instruction_id>25</instruction_id><instruction_text>Fry the onion, rice and celery for about 4 minutes then add the tomatoes, stock, sugar and the herbs. Bring to the boil whilst stirring.</instruction_text></instruction><instruction><instruction_id>26</instruction_id><instruction_text>Pour the mixture over the chicken, cover the casserole dish and cook at 400f, 200c or gas mark 6 for about an hour</instruction_text></instruction><instruction><instruction_id>27</instruction_id><instruction_text>Heat the remaining butter and fry the mushrooms for 2 minutes before adding to the casserole.</instruction_text></instruction></instructions><ingredients/></recipe><recipe><rec_id>19</rec_id><name>clapshot</name><overview>http://www.scottishrecipes.co.uk/clapshot.htm</overview><time>2009-11-10 11:59:53</time><image>localhost/cafe/pics/clapshot.jpg</image><instructions><instruction><instruction_id>28</instruction_id><instruction_text>Boil the tatties and swede in separate pans and then drain.</instruction_text></instruction><instruction><instruction_id>29</instruction_id><instruction_text>Mash the swede and tatties together adding the butter.</instruction_text></instruction><instruction><instruction_id>30</instruction_id><instruction_text>Stir in the chives and season with the salt and pepper</instruction_text></instruction></instructions><ingredients/></recipe><recipe><rec_id>20</rec_id><name>Haggis Recipe</name><overview>http://www.scottishrecipes.co.uk/haggis2.htm</overview><time>2009-11-10 12:03:02</time><image>localhost/cafe/pics/haggis-neeps-tatties.jpg</image><instructions><instruction><instruction_id>31</instruction_id><instruction_text>Clean the stomach bag thoroughly and soak overnight. In the morning turn it inside out.</instruction_text></instruction><instruction><instruction_id>32</instruction_id><instruction_text>Wash the pluck and boil for 1.5 hours, ensuring the windpipe hangs over the pot allowing drainage of the impurities.</instruction_text></instruction><instruction><instruction_id>33</instruction_id><instruction_text>Mince the heart and lungs and grate half the liver.</instruction_text></instruction><instruction><instruction_id>34</instruction_id><instruction_text>Chop up the onions and suet.</instruction_text></instruction></instructions><ingredients/></recipe><recipe><rec_id>21</rec_id><name>Mince and Tatties Recipe</name><overview>http://www.scottishrecipes.co.uk/mince_and_tatties_recipe.htm</overview><time>2009-11-10 12:15:15</time><image>localhost/cafe/pics/Mince-and-Tatties.jpg</image><instructions><instruction><instruction_id>35</instruction_id><instruction_text>Peel the potatoes and carrots</instruction_text></instruction><instruction><instruction_id>36</instruction_id><instruction_text>Make your mashed potatoes as normal.</instruction_text></instruction><instruction><instruction_id>37</instruction_id><instruction_text>hop tatties into thin cubes and bring to the boil and then simmer for about thirty minutes.</instruction_text></instruction><instruction><instruction_id>38</instruction_id><instruction_text>The water is then drained and a small amount of milk and butter or margarine is added before mashing the potatoes.</instruction_text></instruction><instruction><instruction_id>39</instruction_id><instruction_text>This can be done as the mince is cooking and the mashed tatties made ready to time with the mince being ready.</instruction_text></instruction></instructions><ingredients/></recipe></recipes>

Edited by zeeshan_kust: n/a

0

Try this code to check if select query returns at least one row.

<?
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);


$doc = new DomDocument('1.0');
$root = $doc->createElement('recipes');
$root = $doc->appendChild($root);

while($row = mysql_fetch_assoc($resouter)){


$outer = $doc->createElement($table_first);
$outer = $root->appendChild($outer);

 foreach ($row as $fieldname => $fieldvalue) {
    $child = $doc->createElement($fieldname);
    $child = $outer->appendChild($child);
    $value = $doc->createTextNode($fieldvalue);
    $value = $child->appendChild($value);
  }// foreach
 //while
 
//---------------------------- fetch instructions ---------------------------
$table_second='instructions';
$query="SELECT instructions.instruction_id,instructions.instruction_text FROM $table_second where rec_id = ".$row['rec_id'];
$resinner=mysql_query($query, $conn);

$inner = $doc->createElement($table_second);
$inner = $outer->appendChild($inner);
if (mysql_num_rows($resinner) > 0){
while($row = mysql_fetch_assoc($resinner)){
    // add node for each record
    

    $inner1=$doc->createElement('instruction');
    $inner1=$inner->appendChild($inner1);
    // add a child node for each field
    foreach ($row as $fieldname => $fieldvalue) {
        $child = $doc->createElement($fieldname);
        $child = $inner1->appendChild($child);
        $value = $doc->createTextNode($fieldvalue);
        $value = $child->appendChild($value);
    } // foreach
 }}// while


//---------------------------- fetch ingredients ---------------------------
$table_third='ingredients';
$query="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM $table_third where rec_id = ".$row['rec_id'];
$resthird=mysql_query($query, $conn);

$inner=$doc->createElement($table_third);
$inner=$outer->appendChild($inner);

if (mysql_num_rows($resthird) > 0){
while($row=mysql_fetch_assoc($resthird)){



     $inner2=$doc->createElement('ingredient');
    $inner2=$inner->appendChild($inner2);

    foreach($row as $fieldname=> $fieldvalue)
    {
        $child=$doc->createElement($fieldname);
        $child=$inner2->appendChild($child);
        $value=$doc->createTextNode($fieldvalue);
        $value=$child->appendChild($value);
    }
}}

}

mysql_close($conn);
$xml_string = $doc->saveXML();
echo $xml_string;
?>
0

thanks for your reply vibhadevit i make some change in your previous post and it works i changed row to row1 in foreach ($row1 as $fieldname => $fieldvalue) for instruction table it works. the third query take rec_id=.$row; from instruction table now i make this from recipe table and works thanks alot for your interest.

0

once more question vibadevit
now i want the xml to read in another php file so what will be the procedure all the code is shown

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.