Dynamic XML with PHP DOM with many to many relation

PHP
1

I have a mysql database with few tables and the database has got a one
to many relationship. I want to create a dynamic xml file, which will
be updated by php script and the input comes from the mysqldatabase.
For reasons of anoymitiy and ease I will give a example product. My
products are different cars (many) and the equipments are saved in
seperate table and are many.

Now I will show you the database with these two tables (it’s very easy
made for purpose of ease):

cars_db:

CarId | Manufacturer | Color
001 | Volkswagen | RED

002 | Porsche | Yellow
003 | Audi | Black

cars_equipment:

CarId | equipmentname | sortorder
001 | wheels | 01
001 | sensor | 02

001 | led lamp | 03
001 | tool set | 04
002 | led lamp | 01
002 | tool
set | 02
003 | led lamp | 01
003 | tool set | 02

I want to produce following format of xml file

<cars>
   <car id="001">
     <manufacturer>Volkswagen</manufacturer>
     <equipments>
      <equipment>wheels</equipment>
      <equipment>sensor</equipment>
      <equipment>led lamp</equipment>
      <equipment>tool set</equipment>
     </equipments>   
    <car id="002">
     <manufacturer>Porsche</manufacturer>
     <equipments>
      <equipment>led lamp</equipment>
      <equipment>tool set</equipment>
     </equipments>
     <car id="003">
     <manufacturer>Audi</manufacturer>
     <equipments>
      <equipment>led lamp</equipment>
      <equipment>tool set</equipment>
     </equipments>
</cars>

Now my code is like this:

 <?php session_start();
 
 $host_name = 'xyz'; $db_name = 'xyz'; $user_name = 'root'; $password =
 '';
 
 $table = 'cars';
 
 try {
     $conn=new PDO("mysql:host=$host_name; dbname=$db_name", $user_name , $password);
     //$conn->exec("SET CHARACTER SET utf8"); } catch (PDOException $e) {
     echo 'Connection -not- made<br/>';
     print "Error!: " . $e->getMessage() . "<br/>";
     die(); }
 
 $getAll_query = "SELECT cars_db.*, cars_equipment.* FROM cars_db LEFT
 JOIN cars_equipment ON cars_db.CarId = cars_db.CarId";
 
 $xml_statement = $conn->prepare($getAll_query);
 $xml_statement->execute();
 
 $xml=new DOMDocument ("1.0"); $xml->formatOutput=true;
 $machines=$xml->createElement("cars"); $xml->appendChild($cars);
 
 foreach ($xml_statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
     
     $machine=$xml->createElement("car");
     $machine->setAttribute("Car ID",$row['CarsId']);
     $machines->appendChild($cars);
     
     $c_cars_manufacturer=$xml->createElement("Manufacturer",
     htmlspecialchars($row['Manufacturer']));
     $c_cars->appendChild($c_CarsId);
 
     $c_equipment=$xml->createElement("equipment");
     $cars->appendChild($c_equipment);
 
    } echo "<xmp>".$xml->saveXML()."</xmp>"; $xml->save("cars.xml");
 >

Don’t focus on the code, because it’s not very important.
What I need to know is, how can I run equipments so long, that it will display
all equipments like above shown. Or do I need special sql statement.???
In which way I’m able to do so.

I cannot get with PHP done to show it by php script.

I think there should be another loop in the loop, but this doesn’t work either.

2

no, what you have is fine, except there’s a typo –

ON cars_db.CarId = cars_db.CarId

as written, this will give you explosive results, because it is true for every combination of cars and equipment

you can fix it, yes?

after it’s fixed, you should ask to have this thread moved to the php forum

3

Dear Sir,

As I already told, the code has been changed to make it anonymous, so there should be a few typos and so on.

What I want to know, I want to show the car one and with all equipment it has, but I don’t know how to that.

4

It should produce the same output as the above XML code. I need to know, how to accomplish this. I think, the SQL part is okay, but now the php parts needs some help.

I think there must be another loop or something.

5

Are you saying this is a typo that came as a result of editing your code to post in the forum?

If so, would it be possible to see what the real code actually says at this part if it does not say that?

6

Sir,

The code should stay anonymous. But the code is not important. Please just tell me in php fashion, how to list all the cars and all their equipments like in the XML output listed above. And the databases you will see upwards.

How do we solve this problem, where there is one to many relation (one car with many equipments) and I join them and show all equipments.

8

You need to sort by the cars by id. When you do this the cars and equipment will be in sequential order.

car id | equipment

1 | lep lamp
1 | tool set
2 | led lamp

Now what you do is you keep track of the previous car. If the previous car doesn’t match the current car you know you are dealing with a new car.

With a new car you print the opening dom element for a car. For every row that matches that current car you print an equipment line. That is done until you reach a row that doesn’t match the current car. When that happens you close the current car dom element and begin the next one. Same thing over and over.

You could also use that same logic to build a multidimensional array of the flat data coming back for the database. The printing of the data becomes a little easier once you have the hierarchical structure. This isn’t very straight forward though which is why you see a lot of people not use joins and run queries inside loops. However, you can in php take a flat row set with 1:m or m:n relations and map it to hierarchy structure to avoid the n+1 query issue. In traditional ORMS this is referred to as eager loading but the same concept can be applied to procedural code.

9

This is an example of how you can take the flat data from the database and map it to hierarhical structure.

$data = [
 ['id'=> 1, 'equipment_id'=> 23, 'equiment_label'=> 'lens'],
 ['id'=> 1, 'equipment_id'=> 25, 'equiment_label'=> 'wheel'],
 ['id'=> 1, 'equipment_id'=> 27, 'equiment_label'=> 'tires'],
 ['id'=> 2, 'equipment_id'=> 23, 'equiment_label'=> 'lens'],
 ['id'=> 2, 'equipment_id'=> 25, 'equiment_label'=> 'wheel'],
 ['id'=> 3, 'equipment_id'=> 27, 'equiment_label'=> 'tires']
];

$mapped = [];
foreach($data as $row) {
  if(!isset($mapped[$row['id']])) {
      $mapped[$row['id']] = ['id'=> $row['id'], 'equipment'=> []];
  }
  $mapped[$row['id']]['equipment'][$row['equipment_id']] = ['id'=> $row['equipment_id'], 'title'=> $row['equiment_label']];
}
        
print_r($mapped);

Note how the cars are duplicated but the mapped array removes the duplicates and equipment gets placed in a nested array under the car it is associated with.

If the equiipment were duplicated you do the same thing and check for the presence of the equipment and if the equipment already existed skip over it and move to the next relation to check.

Once you have that structure its just a matter of printing out. The tricky part should be converting the flat array to a hierarchical structure.

One of the nice things about using an ORM is robust ones like Doctrine do this all for you. Its referred to as “hydration” in doctrine. You can eager load as many relations as you like and the lib will take care of this mess.

10

Okay thanks Zookeeper, I will try to use the code you provided and try to learn everything you mentioned.

I can tell you even now, that you are my hero.

I hope the code will somehow work with my understand.

11

Just a question : Do I have to convert manually all equipment to of every car I have to hierarchical code. The only thing is, that I have about 13000 equipments and about 120 cars in my dB. This makes about 1.5 Mio. combinations.

Isn’t there a MySQL way of doing that. I nearly did this with my code, but only the equipments and pictures of my cars are (which can be both many) are the current problem. All other things have worked perfectly.

12

as written, this will give you explosive results, because it is true for every combination of cars and equipment

you can fix it, yes?

after it’s fixed, you should ask to have this thread moved to the php forum

I fixed most of my code thanks to Zookeeper, but now I’m stuck with this problem. I get explosive results. Every car will show up in the xml outlput as much equipments they have. So what can I exactly do to avoid this.

I want to show every car and their equipments just one time like in the first post. But there’s really no way.

I did a foreach in a foreach. The equipments show up for the right car and only for the car, but the cars get duplicated.

Here a anomized code:

…for the first array, everything is fine.

foreach ($xml_statement->fetchAll(PDO::FETCH_ASSOC) as $row) {

$machine=$xml->createElement("car");

…now here come the equipments(desription) , so another SELECt statment

foreach ($xml_statement2->fetchAll(PDO::FETCH_ASSOC) as $row2) {

    $m_description=$xml->createElement("description",htmlspecialchars($row2['Desrippy']));
    $car->appendChild($m_description);

But the output is, that the Vokswagen is displayed four time, because it has four equipments.

I just need to know, how can have results unique with all their equipments.

13

So ultimatly I just want, that the results are not true for every combination of cars and equipments.

14

please show your sql

15

Hi @r937,

I’m very sorry, that I have written the garbage like above. However now I have prepared a sample sql (please name it cars) and my php code. You will see, that the php code will show every car with every kind of combination. What I just want is something like this:

<?xml version="1.0"?>
<cars>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type> 
    <Equipments>...</Equipments>
    <Equipments>...</Equipments>
  </Car>
<Car>
 <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>***</Equipments>
    <Equipments>***</Equipments>
 </Car>
</cars>

/>

This is my problem regarding how to do “One to many relational” queries. I’m sorry, but I cannot show you the code in the forum, because xml is hidden :D.

However please download the sql file (name it cars.db, because it’s .txt file) and download the php script and please help me to figure out, what has been wrong. I really have tried for several weeks and I didn’t find a solution.

cars.sql.txt (2.4 KB)

1.php (1.2 KB)

forum post.txt (1.0 KB)

16

Please also read forum post.txt for the real forum post.

17

sorry, my friend, i don’t do php

your sql is fine –

SELECT cars.*, carsequipment.* FROM cars LEFT JOIN carsequipment ON cars.CarId = carsequipment.CarId

each car will be shown with only its equipment

the error must lie in your php

18

Yeah I know, because SQL in only for the easy query the database and php is for the displaying the data in my fashion. I don’t really now, if there should be two queries or where the mistake really is…

19

THE QUERY YOU POSTED IS FINE!!!

20

Yes the sql query should be like this, only php should be somehow fixed. Hope someone can figure the error out. However in the post, there will be the picture of the sql queries result.

596×399 88.9 KB

21

based on your actual data, THOSE QUERY RESULTS ARE CORRECT!!!

INSERT INTO `cars` (`CarId`, `Brand`, `Type`, `Color`) VALUES
(1, 'Audi', 'T8', 'Blue'),
(2, 'BMW', 'X2', 'RED'),
(3, 'VW', 'GOLF 7', 'Black'),
(4, 'Opel', 'Astra', 'Cyan');

INSERT INTO `carsequipment` (`EquipmentId`, `CarId`, `Equipmentdesc`) VALUES
(1, 1, 'Wheels'),
(2, 1, 'Allen wrench sets (standard and metric)'),
(3, 1, 'Roll of shop towels'),
(4, 1, 'Magnet with extendable rod'),
(5, 2, 'Axle nut sockets'),
(6, 2, 'Pick set'),
(7, 2, '1 fender cover'),
(8, 2, 'Roll of shop towels'),
(9, 3, 'Impact universal joint'),
(10, 3, 'Narrow brake spoon'),
(11, 3, 'Tire gauge'),
(12, 3, 'Tire valve core remover'),
(13, 3, 'Universal joint'),
(14, 3, 'Reversible ratchet'),
(15, 3, 'Gasket scraper'),
(16, 4, 'Fuel pressure test kit'),
(17, 4, 'DVOM capable of measuring amperage'),
(18, 4, 'Sparkplug gaper'),
(19, 4, 'Ignition Spark Tester'),
(20, 4, 'Welding kit'),
(21, 1, 'First Aid Kit'),
(22, 1, 'Universal joint');