Writting SQL Data to XML file

[-markie-]

 

I'm trying to write to a xml file from my database. Have it writing to the file ok with a simple string:

--------

$handle = fopen("test.xml", "w");//erase file first

 

$filename = 'test.xml';

 

$somecontent = "Add this text to the file\n";

 

// make sure the file exists and is writable first.

if (is_writable($filename)) {

 

    // that's where $somecontent will go when we fwrite() it.

    if (!$handle = fopen($filename, 'a')) {

        echo "Cannot open file ($filename)";

        exit;

    }

 

    // Write $somecontent to our opened file.

    if (fwrite($handle, $somecontent) === FALSE) {

        echo "Cannot write to file ($filename)";

        exit;

    }

 

    echo "Success, wrote ($somecontent) to file ($filename)";

 

    fclose($handle);

 

} else {

    echo "The file $filename is not writable";

}

?>

--------

 

So how would i go about adding a while loop to it to so it writes all the rows from my database. I was going to manually write the xml tags in.

For example:

<newsitem id=['id']>

<title>['heading']</title>

<content>['description']</content>

<date>['date']</date>

</newsitem>

 

obviously with the php tags... just not sure how to do this loop within the fwrite section.

 

I tried just the normal :

while ($row = mysql_fetch_array($result))

  {                                                                         

      print "  <tr>";                                                       

      print "    <td>" . $row["id"] . "</td>";

      print "    <td>" . $row["date"]. "</td>";

      print "    <td>" . $row["heading"]. "</td>";

      print "    <td>" . $row["content"]. "</td>";

      print "  </tr>";                                                     

  }                                                                         

  print "</table>";

 

}

 

but it wasnt writing it into the file, not sure how i would reference that loop code to the $somecontent variable.

[Kieran Menor]

First off, this line is unnecessary:

 

$handle = fopen("test.xml", "w");//erase file first

 

Simply open the file with 'w' instead of 'a' when you open it for writing.

 

Anyway, I would do it something like this:

 

$result = mysql_query($query);
$handle = fopen($filename, 'w');
while($row = mysql_fetch_assoc($result)) {
fwrite($handle, '<newsitem id="' . $row['id'] . '"><title>' . $row['title'] . '</title><!-- and so forth --></newsitem>');
}
fclose($handle);

[-markie-]

Ah thankyou. That is working correctly now. Just one final question... how do i place the formatting for the xml document outside of that loop.

Here is my code but its gives a error:

$xmlopen = fopen($filename, 'w');
$handle = fopen($filename, 'w');
$xmlclose = fopen($filename, 'w');
while($row = mysql_fetch_assoc($result)) {

fwrite($xmlopen,'<?xml version="1.0" encoding="iso-8859-1"?><?xml-stylesheet type="text/xsl" href="news.xsl"?><news>');


fwrite($handle, '<newsitem id="' . $row['id'] . '"><heading>' . $row['heading'] . '</heading><content>' . $row['content'] . '</content><date>' . $row['date'] . '</date></newsitem>');
}


fwrite($xmlclose,'</news>');
fclose($xmlopen, $handle, $xmlclose);

[-markie-]

Still struggling with this