how to retrieve xml data from three tables of mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
semanticnotion
Forum Newbie
Posts: 1
Joined: Tue Nov 09, 2010 2:57 am

how to retrieve xml data from three tables of mysql

Post by semanticnotion »

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

Code: Select all

$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;
User avatar
saltwine
Forum Newbie
Posts: 16
Joined: Tue Nov 09, 2010 7:05 am
Location: London

Re: how to retrieve xml data from three tables of mysql

Post by saltwine »

Hi semantic


If you say it all works already, it sounds like you've just got the third loop in the wrong place. Try moving it to within your second while loop:

Code: Select all

$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

	$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

		$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);
			}
		}
	}
}// while

mysql_close($conn);
$xml_string = $doc->saveXML();
echo $xml_string;
Post Reply