From SQL Table to XML file using PHP

[olivebibi]

Good evening,
I'm trying to generate an xml file based upon the result of an SQL query. I'm making some good progress so far but there is 2 problems I can seem to solve.
My php code is the following:

<?php


require ("Connection.php");

    
$db_handle = mysql_connect($server, $user_name, $password);


//load the database
mysql_select_db($database);


//Create file
$file = fopen("http://192.168.56.101/IRMA_server/phpFiles/results.xml",w);


//Display Document in browser as plain text for readabiity purposes
header("Content-type: text/xml;charset=utf-8");


//Obtain the number of phases
$Query = "SELECT MAX(Phase_ID) AS NbPhase FROM Improvements";   
$Result = mysql_query($Query);
$Row = mysql_fetch_object($Result);
$nbPhase = $Row->NbPhase; 


print "<TrafficPhase>\n";

$i = 1;
while ($i <= $nbPhase)
{
    print "<Phase>\n";

    $k = 1;
    $Query1 = "SELECT ImpName, Phase, Option_ID, COUNT(Option_ID) AS NbOption FROM Improvements WHERE Phase_ID = $i"; 
    $Result1 = mysql_query($Query1);
    $Row1 = mysql_fetch_object($Result1);
    
    
    print "<PhaseName>".$Row1->Phase."</PhaseName>\n";
    
    print "<Option_id>" .$k. "</Option_id>\n";  
    print "<name>".$Row1->ImpName."</name>\n";
    print "<Option_Concept_ID>".$Row1->Option_ID."</Option_Concept_ID>\n";
    
    $k = $k + 1;
    
    $Query2 = "SELECT ImpName, Option_ID FROM Improvements WHERE Phase_ID = $i"; 
    $Result2 = mysql_query($Query2);
    $Row2 = mysql_fetch_object($Result2);
    

    while($Row2 = mysql_fetch_object( $Result2)) 
    {
        print "<Option_id>" .$k. "</Option_id>\n";
        print "<name>".$Row2->ImpName."</name>\n";
        print "<Option_Concept_ID>".$Row2->Option_ID."</Option_Concept_ID>\n";
        $k = $k + 1;
        
    }
    
    
    $i = $i + 1;
    print "</Phase>\n";
}

print "</TrafficPhase>\n";
$xml->formatOutput = true;
echo $xml->saveXML();
fwrite($file, $xml->asXML());
fclose($file);
?>

The resulting xml tree is:

<TrafficPhase>
<Phase>
<PhaseName>Approach Departure Transition</PhaseName>
<Option_id>1</Option_id>
<name>Better Predictability</name>
<Option_Concept_ID>1</Option_Concept_ID>
<Option_id>2</Option_id>
<name>Increase Arrival Throughput</name>
<Option_Concept_ID>2</Option_Concept_ID>
</Phase>
<Phase>
<PhaseName>Final Approach/Initial Departure</PhaseName>
<Option_id>1</Option_id>
<name>Enhance Arrival/Departure Sequence</name>
<Option_Concept_ID>3</Option_Concept_ID>
<Option_id>2</Option_id>
<name>Increase Capacity of Parallel Runways</name>
<Option_Concept_ID>4</Option_Concept_ID>
<Option_id>3</Option_id>
<name>Reduce Separation</name>
<Option_Concept_ID>5</Option_Concept_ID>
<Option_id>4</Option_id>
<name>Shorten Approach Phase</name>
<Option_Concept_ID>6</Option_Concept_ID>
</Phase>
<Phase>
<PhaseName>Surface</PhaseName>
<Option_id>1</Option_id>
<name>Improve Throughput</name>
<Option_Concept_ID>7</Option_Concept_ID>
</Phase>
</TrafficPhase>

But I would like to obtain this instead:

<TrafficPhase>
<Phase>
<PhaseName>Approach Departure Transition</PhaseName>
[B]<Option id = "1">[/B]
<name>Better Predictability</name>
<Option_Concept_ID>1</Option_Concept_ID>
[B]</Option>[/B]
[B]<Option id = "2">[/B]
<name>Increase Arrival Throughput</name>
<Option_Concept_ID>2</Option_Concept_ID>
[B]</Option>[/B]
</Phase>
<Phase>
<PhaseName>Final Approach/Initial Departure</PhaseName>
[B]<Option id = "1">[/B]
<name>Enhance Arrival/Departure Sequence</name>
<Option_Concept_ID>3</Option_Concept_ID>
[B]</Option>[/B]
[B]<Option id = "2">[/B]
<name>Increase Capacity of Parallel Runways</name>
<Option_Concept_ID>4</Option_Concept_ID>
[B]</Option>[/B]
[B]<Option id = "3">[/B]
<name>Reduce Separation</name>
<Option_Concept_ID>5</Option_Concept_ID>
[B]</Option>[/B]
[B]<Option id = "4">[/B]
<name>Shorten Approach Phase</name>
<Option_Concept_ID>6</Option_Concept_ID>
[B]</Option>[/B]
</Phase>
<Phase>
<PhaseName>Surface</PhaseName>
[B]<Option id = "1">[/B]
<name>Improve Throughput</name>
<Option_Concept_ID>7</Option_Concept_ID>
[B]</Option>[/B]
</Phase>
</TrafficPhase>

How should I modify my php file in order to obtain this result? The value for "Option id =" corresponds to the variable $k in my script.
Also, whatever I've been trying, I cannot save the xml in a file (either on my server or hardrive).
I'm a newbie and learning php as I go. Your help would be greatly appreciated :)
Thank you in advance!

Olivebibi

[P0lT10n]

When you do the while for print/save or what ever, you do something wrong

print "<Option_id>" .$k. "</Option_id>\n";

If you want to save $k like

<Option id="NUMBER_OF_$k">

You should do this

print "<Option id='".$k."'>";
print "<name>".$Row1->ImpName."</name>\n";
print "<Option_Concept_ID>".$Row1->Option_ID."</Option_Concept_ID>\n";
print "</Option>\n";

Because with

print "<Option_id>" .$k. "</Option_id>\n";

You are saving between that tags the number... if you want to save something, you must always print or echo the code to be saved.

[olivebibi]

Thank you very much,

print "<Option id='".$k."'>";

works great!!
I still cannot save the xml tree into an xml file though... I'm using:

echo $xml->saveXML();
fwrite($file, $xml->asXML());

The php file is on my server (Ubuntu server on Virtual Machine). Is it possible to save the file in a folder on my hard-drive? I use a mac and tried

//$file = fopen("Users/olivebibi/Documents/Flex Builder 3/IRMAonServer/src/result.xml",w);

but that doesn't seem to work.
Anyhow, thank you again very much :)

[P0lT10n]

;) i think that this dont work because you used $xml->asXML() and you can put there w, r or a... if asXML is w ok... but if not, use w ! and dont forget to mark it as solved when your code works !