Export data from MySQL to XML file of complex structure?

Question

PHP

Hello! There was a problem:
There is a MySQL table from which you need to create an XML file of a given sample. Everything would be fine, only this file has a rather complex structure with multiple nesting.
If you go through the rows of the table with a WHILE loop, you get the following file structure:

<street name="имя">
<house number="номер">
<apartment number="номер">
<account id="номер" person="">
<device id="номер" value="номер" date="дата" type="1"/>
</account>
</apartment>
</house>
</street>
<street name="имя">
<house number="номер">
<apartment number="номер">
<account id="номер" person="">
<device id="номер" value="номер" date="дата" type="1"/>
</account>
</apartment>
</house>
</street>
<street name="имя">
<house number="номер">
<apartment number="номер">
<account id="номер" person="">
<device id="номер" value="37" date="дата" type="1"/>
</account>
</apartment>
</house>
</street>
<street name="имя">
<house number="номер">
<apartment number="номер">
<account id="номер" person="">
<device id="номер" value="45" date="дата" type="1"/>
</account>
</apartment>
</house>
</street>
<street name="имя">
<house number="номер">
<apartment number="номер">
<account id="номер" person="">
<device id="номер" value="11" date="дата" type="1"/>
</account>
</apartment>
</house>
</street>
<street name="имя">

and you need something like this:

<street name="имя">
  <house number="номер">
    <apartment number="номер">
      <account id="номер" person="">
        <device id="номер" value="номер" date="дата" type="1"/>
        <device id="номер" value="номер" date="дата" type="1"/>
      </account>
    </apartment>
    <apartment number="номер">
      <account id="номер" person="">
        <device id="номер" value="37" date="дата" type="1"/>
        <device id="номер" value="45" date="дата" type="1"/>
      </account>
    </apartment>
    <apartment number="номер">
      <account id="номер" person="">
        <device id="номер" value="11" date="дата" type="1"/>
        <device id="номер" value="39" date="дата" type="1"/>
      </account>
    </apartment>
  </house>
</street>

Here is the PHP code in which I am still trying to display such a structure on the screen, without generating an XML file:

// Составляем запрос по выбору кода и названия страны из таблицы
        $query = mysql_query("SELECT * FROM `$table` "); 
        
            // Затем в цикле разбираем запрос, и формируем XML
            while ($row = mysql_fetch_array($query)) {               
        $street = $row['street'];
        $house_number = $row['house_number'];
        $new_meterage = $row['new_meterage'];
        $app_number = $row['app_number'];
        $description = $row['description'];
        $old_meterage = $row['old_meterage'];
        $device_id = $row['device_id'];
        $id = $row['id'];
        echo "<br>'$street'";
        echo "<br>'$house_number'";
        echo "'$app_number'";
        echo "'$device_id'";
        echo "'$id'";
        echo "'$description'";
        echo "'$old_meterage'";
        echo "'$new_meterage'";
        }

Answer 1

If without using classes to work with XML, then something like this:

$query = mysql_query("SELECT * FROM `$table` SORT BY street, house_number, app_number ");

// Затем в цикле разбираем запрос, и формируем XML
$xml = '<?xml version="1.0" encoding="utf-8"?>' . PHP_EOL;
$prev_street = null;
$prev_house = null;
$prev_app = null;
$prev_acc = null;

while ($row = mysql_fetch_array($query)) {
    if ($prev_acc != null && $row['account'] != $prev_acc) {
        $xml .= '</account>' . PHP_EOL;
    }
    if ($prev_app != null && $row['app_number'] != $prev_app) {
        $xml .= '</apartment>' . PHP_EOL;
    }
    if ($prev_house != null && $row['house_number'] != $prev_house) {
        $xml .= '</house>' . PHP_EOL;
    }
    if ($prev_street != null && $row['street'] != $prev_street) {
        $xml .= '</street>' . PHP_EOL;
    }

    if ($row['street'] != $prev_street) {
        $xml .= '<street name="' . $row['street'] . '">' . PHP_EOL;
        $prev_street = $row['street'];
    }

    if ($row['house_number'] != $prev_house) {
        $xml .= '<house number="' . $row['house_number'] . '">' . PHP_EOL;
        $prev_house = $row['house_number'];
    }

    if ($row['app_number'] != $prev_app) {
        $xml .= '<apartment number="' . $row['app_number'] . '">' . PHP_EOL;
        $prev_app = $row['app_number'];
    }

    if ($row['account'] != $prev_acc) {
        $xml .= '<account id="' . $row['account'] . '" person="">' . PHP_EOL;
        $prev_acc = $row['account'];
    }

    $xml .= '<device id="' . $row['device_id'] . '" value="' . $row['new_meterage'] . '" date="дата" type="1"/>' . PHP_EOL;
}
$xml .= '</account>' . PHP_EOL . '</apartment>' . PHP_EOL . '</house>' . PHP_EOL . '</street>';

echo $xml;

Answer 2

Thank you Everves as always!
The code works almost perfectly, but for some reason it does not set the closing tags correctly
. Here is a piece of the finished XML:

<house number="7">
<apartment number="3">
<account id="14011" person="">
<device id="182757" value="" date="дата" type="1"/>
</account>
<device id="183271" value="" date="дата" type="1"/>
</account>
</apartment>
<apartment number="6">
<account id="14011" person="">
<device id="15865" value="" date="дата" type="1"/>
</account>
<device id="16421" value="" date="дата" type="1"/>
</account>
</apartment>
<apartment number="48">
<account id="140110" person="">
<device id="31948" value="" date="дата" type="1"/>
</account>
<device id="31948" value="" date="дата" type="1"/>
</account>
</apartment>
</house>