| how to retrieve xml data from three tables of mysql [message #170591] |
Tue, 09 November 2010 10:40  |
hunter
Messages: 1
Registered: November 2010
Karma: 0
|
Junior Member |
|
|
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;
|
|
|
|
| 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: 0
|
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
==================
|
|
|
|
|
|
| Re: how to retrieve xml data from three tables of mysql [message #170594 is a reply to message #170593] |
Tue, 09 November 2010 13:43 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/9/2010 8:34 AM, Luuk wrote:
> On 09-11-10 14:22, Jerry Stuckle wrote:
>>
>> You need to learn how to use SQL effectively, but that isn't PHP. Try
>> comp.databases.mysql.
>>
>
> no, he needs to learn how to use PHP effectively.....
>
No, the row selection is a SQL problem. Once that's done he can get the
PHP straightened out.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: how to retrieve xml data from three tables of mysql [message #170595 is a reply to message #170594] |
Tue, 09 November 2010 13:46 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 09-11-10 14:43, Jerry Stuckle wrote:
> On 11/9/2010 8:34 AM, Luuk wrote:
>> On 09-11-10 14:22, Jerry Stuckle wrote:
>>>
>>> You need to learn how to use SQL effectively, but that isn't PHP. Try
>>> comp.databases.mysql.
>>>
>>
>> no, he needs to learn how to use PHP effectively.....
>>
>
> No, the row selection is a SQL problem. Once that's done he can get the
> PHP straightened out.
>
But, at first, he should understand what his PHP-code does,
before he can realize that he has a problem with his SQL
--
Luuk
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]