Trying to write Php fwrite XML file from mysql query

Question

I am trying to write some code that grabs a CSV file pulls the relevant postcode column then looks up that postcode in a MySql database which has longitude and latitude fields then save them to an XML file so it can be used in a different program

I think this code piece is all working, but for some reason, it only outputs the last field of the query :

//Open the file.
$fileHandle = fopen("test.csv", "r");
$postcode = array();
while (($row = fgetcsv($fileHandle, 0, ",")) !== FALSE) {
 array_push($postcode, $row[40]);
}
$postcodefil = (array_unique($postcode));
$postcodefil = str_replace(' ', '', $postcodefil);
$postcodefil = preg_replace('/\s+/', '', $postcodefil);
//print_r($postcodefil);
foreach ($postcodefil as $value) {
  // Create connection
  $conn = new mysqli($servername, $username, $password,   $dbname);
  // Check connection
  if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
  }
  $sql = "SELECT postcode, latitude, longitude  FROM postcode WHERE postcode='$value' ";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {
// output data of each row
    while($row = $result->fetch_assoc()) {
          $myfile = fopen("test.xml", "w") or die("Unable to open file!");
        $lat = $row["latitude"];
        $lng = $row["longitude"];
        fwrite($myfile, $lat."testss".$lng."\n");
        echo $lat;
        echo $lng;
        echo "<br />";
        }}
} // end of foreach
$conn->close();

however when i run it, it echo's correctly

50.822398-0.139938
51.444908-1.295341
50.841951-0.842508
51.308504-0.551835
etc.... etc...

but the Fwrite just outputs the last line

51.120916testss-0.599545

I' m totally confused by this. Please forgive me if it's something basic that I've over looked and thanks in advance.

Answer 1

The problem is that you open the file in each loop, this overwrites the previous data...

   $myfile = fopen("test.xml", "w") or die("Unable to open file!");
   while($row = $result->fetch_assoc()) {

So move the open outside the loop.

The second issue is that you aren't writing XML at all. You need to do something like...

$xml = simplexml_load_string("<coords />");

while($row = $result->fetch_assoc()) {
    $newCoord = $xml->addChild("coord");
    $newCoord->addChild("latitude", $row["latitude"]);
    $newCoord->addChild("longitude", $row["longitude"]);
}
$xml->saveXML("test.xml");

This will generate a simple XML file, you will need to set the element names as appropriate.