Dynamic XML with PHP DOM with many to many relation

[noname9797]

@noname9797

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.

I know, this are the results I have to get. I just need a php dev to help me out now. Thanks

[noname9797]

Okay a little bit near to the right course. The php course has been improved and will show the right equipment for the right car. But still the cars are duplicated.

The code:

$con=mysql_connect("localhost","root","") or die ("Connection Failed");
$db=mysql_select_db("cars",$con);

$result = mysql_query("SELECT cars.*, carsequipment.Equipmentdesc 
FROM cars LEFT JOIN carsequipment ON cars.CarId = carsequipment.CarId");


//$result = mysql_query("SELECT cars.*, carsequipment.Equipmentdesc 
//FROM cars LEFT JOIN carsequipment ON cars.CarId = carsequipment.CarId WHERE cars.CarId = 1 ");


//$result = mysql_query("SELECT * FROM cars");


$xml=new DOMDocument ("1.0");
$xml->formatOutput=true;
$cars=$xml->createElement("cars");
$xml->appendChild($cars);

while($row=mysql_fetch_array($result))
{
$car=$xml->createElement("Car");
$cars->appendChild($car);    

$brand=$xml->createElement("Brand",$row['Brand']);
$car->appendChild($brand);

$brand=$xml->createElement("Type",$row['Type']);
$car->appendChild($brand);

$result2 = mysql_query("SELECT cars.*, carsequipment.* 
FROM cars LEFT JOIN carsequipment ON cars.CarId = carsequipment.CarId HAVING cars.CarId ={$row['CarId']}");

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

$brand=$xml->createElement("Equipments",$row['Equipmentdesc']);
$car->appendChild($brand);

}

}

echo "<xmp>".$xml->saveXML()."</xmp>"; $xml->save("cars.xml");

[noname9797]

Here are the results I get in XML:

<?xml version="1.0"?>
<cars>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
  <Car>
    <Brand>BMW</Brand>
    <Type>X2</Type>
    <Equipments>Axle nut sockets</Equipments>
    <Equipments>Pick set</Equipments>
    <Equipments>1 fender cover</Equipments>
    <Equipments>Roll of shop towels</Equipments>
  </Car>
  <Car>
    <Brand>BMW</Brand>
    <Type>X2</Type>
    <Equipments>Axle nut sockets</Equipments>
    <Equipments>Pick set</Equipments>
    <Equipments>1 fender cover</Equipments>
    <Equipments>Roll of shop towels</Equipments>
  </Car>
  <Car>
    <Brand>BMW</Brand>
    <Type>X2</Type>
    <Equipments>Axle nut sockets</Equipments>
    <Equipments>Pick set</Equipments>
    <Equipments>1 fender cover</Equipments>
    <Equipments>Roll of shop towels</Equipments>
  </Car>
  <Car>
    <Brand>BMW</Brand>
    <Type>X2</Type>
    <Equipments>Axle nut sockets</Equipments>
    <Equipments>Pick set</Equipments>
    <Equipments>1 fender cover</Equipments>
    <Equipments>Roll of shop towels</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>VW</Brand>
    <Type>GOLF 7</Type>
    <Equipments>Impact universal joint</Equipments>
    <Equipments>Narrow brake spoon</Equipments>
    <Equipments>Tire gauge</Equipments>
    <Equipments>Tire valve core remover</Equipments>
    <Equipments>Universal joint</Equipments>
    <Equipments>Reversible ratchet</Equipments>
    <Equipments>Gasket scraper</Equipments>
  </Car>
  <Car>
    <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>Fuel pressure test kit</Equipments>
    <Equipments>DVOM capable of measuring amperage</Equipments>
    <Equipments>Sparkplug gaper</Equipments>
    <Equipments>Ignition Spark Tester</Equipments>
    <Equipments>Welding kit</Equipments>
  </Car>
  <Car>
    <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>Fuel pressure test kit</Equipments>
    <Equipments>DVOM capable of measuring amperage</Equipments>
    <Equipments>Sparkplug gaper</Equipments>
    <Equipments>Ignition Spark Tester</Equipments>
    <Equipments>Welding kit</Equipments>
  </Car>
  <Car>
    <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>Fuel pressure test kit</Equipments>
    <Equipments>DVOM capable of measuring amperage</Equipments>
    <Equipments>Sparkplug gaper</Equipments>
    <Equipments>Ignition Spark Tester</Equipments>
    <Equipments>Welding kit</Equipments>
  </Car>
  <Car>
    <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>Fuel pressure test kit</Equipments>
    <Equipments>DVOM capable of measuring amperage</Equipments>
    <Equipments>Sparkplug gaper</Equipments>
    <Equipments>Ignition Spark Tester</Equipments>
    <Equipments>Welding kit</Equipments>
  </Car>
  <Car>
    <Brand>Opel</Brand>
    <Type>Astra</Type>
    <Equipments>Fuel pressure test kit</Equipments>
    <Equipments>DVOM capable of measuring amperage</Equipments>
    <Equipments>Sparkplug gaper</Equipments>
    <Equipments>Ignition Spark Tester</Equipments>
    <Equipments>Welding kit</Equipments>
  </Car>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
  <Car>
    <Brand>Audi</Brand>
    <Type>T8</Type>
    <Equipments>Wheels</Equipments>
    <Equipments>Allen wrench sets (standard and metric)</Equipments>
    <Equipments>Roll of shop towels</Equipments>
    <Equipments>Magnet with extendable rod</Equipments>
    <Equipments>First Aid Kit</Equipments>
    <Equipments>Universal joint</Equipments>
  </Car>
</cars>

[ZooKeeper]

I’m failing to see my suggestion applied to your solution. From what I have gauged only a single query is needed to fetch the cars and equipments then PHP will used to hydrate / pre-process the result set to remove the duplicates before finally looping through the result set and creating an XML doc. It looks like the first query is mostly correct besides for the selecting a field that can be used to identify each unique car equipment row and rebuild the result set in PHP without duplicates.