Page 1 of 1

Write to XML file with certain formatting

Posted: Sun Feb 11, 2007 8:19 am
by JimiH
Hello

I have the following PHP code which produces a table with "Subcategory" & "Time"

Code: Select all

include("conn.php");


$result = mysql_query("SELECT subcategory, SUM(time) FROM live inner join category on cat = cat_id inner join subcategory on subcat = subcat_id where cat = '1' AND Date BETWEEN '2006-10-01'AND'2009-10-04' GROUP BY subcat") or die(mysql_error());

echo "<table Align ='center' border='1'>"; 
echo "<tr> <th>Sub Category</th> <th>Time</th></tr>";

 // keeps getting the next row until there are no more to get 
while($row = mysql_fetch_array( $result )) { 
    
    
// Print out the contents of each row into a table 
echo "<tr><td>"; 
echo $row['subcategory']; 
echo "</td><td>"; 
echo $row['SUM(time)']; 
echo "</td><td>"; 

 } 
echo "</table>";
I wish to write the values to an XML file with certain formatting as follows

Code: Select all

<Pie title="Sustaining Engineering YTD">
<Data title="Manufacturing" value="0.60" pullOut="true"/>
<Data title="Administration" value="0.60" pullOut="false"/>
<Data title="Approvals" value="0.40" pullOut="false"/>
<Data title="IT" value="2.40" pullOut="false"/>
<Data title="Engineering Documents" value="3.60"/>
</Pie>
The "Data title=" are the "subcategory" and the "value=" is the "Time"

Hope this makes sence

Thanks

geoff

Posted: Sun Feb 11, 2007 8:51 am
by feyd
What problem are you having?

Posted: Sun Feb 11, 2007 8:53 am
by JimiH
I dont know how to write the values to an XML file with the following formating.

Code: Select all

<Pie title="Sustaining Engineering YTD"> 
<Data title="Manufacturing" value="0.60" pullOut="true"/> 
<Data title="Administration" value="0.60" pullOut="false"/> 
<Data title="Approvals" value="0.40" pullOut="false"/> 
<Data title="IT" value="2.40" pullOut="false"/> 
<Data title="Engineering Documents" value="3.60"/> 
</Pie>
Thanks

geoff

Posted: Sun Feb 11, 2007 9:10 am
by feyd
Well, without using an XML extension, it would be done with fopen() + fwrite() + fclose().

I'm not seeing how you are extracting the attribute values however.

Posted: Sun Feb 11, 2007 9:18 am
by JimiH
The values are extracted via this PHP

Code: Select all

// keeps getting the next row until there are no more to get 
while($row = mysql_fetch_array( $result )) { 
    
    
// Print out the contents of each row into a table 
echo "<tr><td>"; 
echo $row['subcategory']; 
echo "</td><td>"; 
echo $row['SUM(time)']; 
echo "</td><td>"; 

 }
Each time it loops through the query I would like to write the result to the XML file like this

<Data title="Manufacturing" value="0.60" pullOut="true"/>

Where Manufacturing is the 'subcategory' and the value is the 'SUM(time)'

It will keep on adding lines to the XML until the query has no more records.

Thanks

Geoff

Posted: Sun Feb 11, 2007 9:22 am
by feyd
Okay, so what have you tried with the links I've posted thus far?

Posted: Sun Feb 11, 2007 9:36 am
by JimiH
Yes, I'm working my way through now.

Thanks for the tips

Geoff

Posted: Mon Feb 12, 2007 6:29 am
by JimiH
Right nearly there, I just cant get one piece of info ("SUM(time)") to come out in the XML

It is wrote as "Array SUM(time)"

Code: Select all

while($row = mysql_fetch_array( $result )) { 
    
$somecontent = "<Data title=\"$row[subcategory]\" value=\"$row SUM(time)\" pullOut=\"true\"/>"; 
 
if (fwrite($handle, $somecontent) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
The XML is produced as follows

Code: Select all

<Pie title="Sustaining Engineering" YTD>
<Data title="Manufacturing" value="Array SUM(time)" pullOut="true"/>
<Data title="Administration" value="Array SUM(time)" pullOut="true"/>
<Data title="Repairs" value="Array SUM(time)" pullOut="true"/>
<Data title="Approvals" value="Array SUM(time)" pullOut="true"/>
<Data title="IT" value="Array SUM(time)" pullOut="true"/>
<Data title="Engineering Documents" value="Array SUM(time)" pullOut="true"/>
So the $row[subcategory] (Manufacturing, Administration etc) is working but the $row SUM(time) is producing "Array SUM(time)"

Hope you can help

Thanks

Geoff

Posted: Mon Feb 12, 2007 9:50 am
by feyd
You're missing something. Some that's in "$row[subcategory]" and not in "$row SUM(time)"

Posted: Mon Feb 12, 2007 11:02 am
by JimiH
Fixed it, I put the value in a variable, "$sumtime"

Code: Select all

while($row = mysql_fetch_array( $result )) { 

$sumtime = $row['SUM(time)'];       
    
$somecontent = "<Data title=\"$row[subcategory]\" value=\"$sumtime\" pullOut=\"true\"/>"; 
 
if (fwrite($handle, $somecontent) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
Now it works fine.

Another question regarding the destination XML file

Code: Select all

$filename = 'sustaining.xml';
How do I specify a specific directory.

Thanks

Geoff

Posted: Mon Feb 12, 2007 11:15 am
by volka
relative path: path/to/directory/filename ( relative to the current working directory, see http://de2.php.net/getcwd )
absolute path: /path/to/directory/filename