| Re: how to retrieve xml data from three tables of mysql [message #170592 is a reply to message #170591] |
Tue, 09 November 2010 13:22   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 11/9/2010 5:40 AM, hunter wrote:
> 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;
You need to learn how to use SQL effectively, but that isn't PHP. Try
comp.databases.mysql.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]