How to retrieve xml data from three tables of mysql

Databases
1

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

not a lot, as we don’t really know how the three tables are related

besides, if it’s as i suspect it is, there are multiple instructions per recipe, and multiple ingredients per recipe, so it has to be at least two separate queries, or else some fancy use of GROUP_CONCAT or something, if you really want to retrive the data from all three tables in a single query

seems this thread was best left in the php forum

3

Hi, welcome to SitePoint.

You really need to concentrate on getting your data from all the tables in one go first, once you’ve cracked that we can look at the code to product the XML.

I’ll ask for this to be moved to the MySQL forum. :slight_smile:

4

I think it will help you with some modifications related to your application::


$result = mysql_query("select field_name from table1");

while($data=mysql_fetch_row($result))
{
	print("create root node with----$data[0]");
	$result1 = mysql_query("select field_name1 from table2 where field_name='$data[0]'");
	if (@mysql_num_rows($result1)!=0)
	{
	print "childnode";
	while($data1=mysql_fetch_row($result1))
	{
	print("$data1[0]");
	$result2 = mysql_query("select field_name2 from table3 where field_name1='$data1[0]'");
	if (@mysql_num_rows($result2)!=0) 
	{
	print "create chile node";
	while($data2=mysql_fetch_row($result2))
	print "leaf-node";
	}	
	print "close relevant nodes";
	}
	print "close child";
	}
	print "close parent";
}
5

No, you don’t want to do this. This will result in far too many database queries being executed, you should be able to pull all the data you need out with just one.

Should. :stuck_out_tongue:

Let’s see what the folks in the MySQL forum say. :slight_smile:

6

hi guys thanks alot for your reply i changed my code a little and it works for me i change second query and add this at the end where rec_id=.$row[‘rec_id’];

Now my question is what should i do if i want to read this xml file from client side in php and changed the data base accordingly.
any help will be appreciated.

7

the three tables are related with (rec_id) as a primary key in recipe table and as a foreign key in ingredients and instructions table i am succeeded to get the xml data from all these three table. now my question is how can i call this php file to retrieve only the xml data in another php file.

i have used this but it does’t work for me

$input = file_get_contents("recipe_xml.php");

$xml = new SimpleXMLElement($input);


$recipe=$xml.recipe;
$rec_id=$xml.rec_id;

echo '$recipe';
echo '$rec_id';

This give me the error:

Warning: SimpleXMLElement::__construct(): Entity: line 100: parser error : Start tag expected, ‘<’ not found in /var/www/cafe/view/update_xml.php on line 10 Call Stack: 0.0005 319164 1. {main}() /var/www/cafe/view/update_xml.php:0 0.0009 322784 2. SimpleXMLElement->__construct() /var/www/cafe/view/update_xml.php:10 Warning: SimpleXMLElement::__construct(): ?> in /var/www/cafe/view/update_xml.php on line 10 Call Stack: 0.0005 319164 1. {main}() /var/www/cafe/view/update_xml.php:0 0.0009 322784 2. SimpleXMLElement->__construct() /var/www/cafe/view/update_xml.php:10 Warning: SimpleXMLElement::__construct(): ^ in /var/www/cafe/view/update_xml.php on line 10 Call Stack: 0.0005 319164 1. {main}() /var/www/cafe/view/update_xml.php:0 0.0009 322784 2. SimpleXMLElement->__construct() /var/www/cafe/view/update_xml.php:10 Exception: String could not be parsed as XML in /var/www/cafe/view/update_xml.php on line 10 Call Stack: 0.0005 319164 1. {main}() /var/www/cafe/view/update_xml.php:0 0.0009 322784 2. SimpleXMLElement->__construct() /var/www/cafe/view/update_xml.php:10

8

seems this thread was best left in the php forum

:slight_smile:

9

r937,

So, are you saying the best strategy for obtaining the required would be to use multiple queries?

semanticnotion, can you provide a SHOW CREATE TABLE for each of the relevant tables and a brief overview saying how they are related?

For what it’s worth, I still think we need to concentrate on how we obtain the data in an efficient manner before we start using it.

10

yup :slight_smile: