asked on

create tree from mysql using php...

I am trying to create a tree menu like parent & child categories in a hirearchy. but the question is the parent and nodes listed under different rows and the parent should be sorted according to the CatName1(order) and the child should also sort by ChildName(order) before displaying...some help please...

My sql table is structured like this.

Product name catName1List CatName1(order) childNameList ChildName(order)
============================================================
434 | Cat1 | 0 | Child1 | 0
344 | Cat1 | 1 | Child1 | 2
432 | Cat2 | 1 | Child2 | 0
435 | Cat2 | 0 | Child2 | 1
433 | Cat3 | 2 | Child3 | 1

As you can see from the above table structure, i have first parent node should be ordered by CatName1(order), and the child node should be ordered by ChildName(order), also what exactly i want to achieve from this table is to get like this structure..

Cat 1
|____ChildB
|_434
____ChildA
|_344
Cat 2
|____ChildA
|_432
____ChildE
|_435
Cat 3
|____Child H
|_433

and so on.
How would i query mySQL to get this result or php script? please help...and then i need to create xml file structure like this...

<Products>product lists</products>
<lists>
<catName1List>Cat1</catName1List>
<childNameList>ChildB </childNameList>
<Product name>434 </Product name>
<childNameList>ChildA</childNameList>
<Product name>344</Product name>
</lists>
<lists>
<catName1List>Cat2</catName1List>
<childNameList>ChildA </childNameList>
<Product name>432 </Product name>
<childNameList>ChildE</childNameList>
<Product name>435</Product name>
</lists>
<lists>
<catName1List>Cat3</catName1List>
<childNameList>ChildH </childNameList>
<Product name>433 </Product name>
</lists>

sara.

NerdsOfTech

I foresee major sorting issues beyond cat10 as cat10 will be between ca1 and cat2

cat1
cat10
cat11
cat2

Is there a reason why this is sorted in this way or is this just example names of catName1?

NerdsOfTech

XML Psuedo Structure:

<A>
 <B>
  <C>
   <D></D>
  </C>
  <C>
   <D></D>
  </C>
 </B>
 <B>
  <C>
   <D></D>
  </C>
  <C>
   <D></D>
  </C>
 </B>
 <B>
  <C>
   <D></D>
  </C>
 </B>
</A>
 
WHERE A = CatName1
WHERE B = ChildNameList
WHERE C = ProductName

Open in new window

NerdsOfTech

Enhanced XML List.

Is this the format that you want to achieve?

Thanks,
NerdsOfTech

<Products>product lists</products>
<lists>
 <catName1List>Cat1
  <childNameList>ChildB 
   <Product name>434</Product name>
   <Product name>344</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat2
  <childNameList>ChildA 
   <Product name>432</Product name>
   <Product name>435</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat1
  <childNameList>ChildB 
   <Product name>433</Product name>
  </childNameList>
 </catName1List>
</lists>

Open in new window

NerdsOfTech

Corrected.

Is this the correct structure first of all before I code....

<Products>product lists</products>
<lists>
 <catName1List>Cat1
  <childNameList>ChildB 
   <Product name>434</Product name>
   <Product name>344</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat2
  <childNameList>ChildA 
   <Product name>432</Product name>
   <Product name>435</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat3
  <childNameList>ChildH 
   <Product name>433</Product name>
  </childNameList>
 </catName1List>
</lists>

Open in new window

NerdsOfTech

Corrected again:

Let me know the structure more clearly before I code the PHP

Thanks,

<Products>product lists</products>
<lists>
 <catName1List>Cat1
  <childNameList>ChildB 
   <Product name>434</Product name>
  </childNameList>
  <childNameList>ChildA 
   <Product name>344</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat2
  <childNameList>ChildA 
   <Product name>432</Product name>
  </childNameList>
  <childNameList>ChildE 
   <Product name>435</Product name>
  </childNameList>
 </catName1List>
 <catName1List>Cat1
  <childNameList>ChildB 
   <Product name>433</Product name>
  </childNameList>
 </catName1List>
</lists>

Open in new window

saad220

ASKER

<Products>product lists</products>
<lists>
<catName1List>Cat1
<childNameList>ChildB
<Product name>434</Product name>
</childNameList>
<childNameList>ChildA
<Product name>344</Product name>
</childNameList>
</catName1List>
<catName1List>Cat2
<childNameList>ChildA
<Product name>432</Product name>
</childNameList>
<childNameList>ChildE
<Product name>435</Product name>
</childNameList>
</catName1List>
<catName1List>Cat1
<childNameList>ChildB
<Product name>433</Product name>
</childNameList>
</catName1List>
</lists>

this approach is perfect where is the php script file?

saad220

ASKER

i have this code by the way but i get errorr..says..Fatal error: Call to a member function fetchAll() on a non-object in C:\Programme\xampp\htdocs\Database\tree.php on line 23..

<?php

// DB credentials
$mysqlhost = 'localhost';
$mysqluser = 'root';
$mysqlpass = '';
$dbname = 'accessDB';

try {
      $db = new PDO("mysql:host={$mysqlhost};dbname={$dbname}", $mysqluser, $mysqlpass, array(PDO::ATTR_PERSISTENT => true));
}
catch(PDOException $e) {
      echo $e->getMessage();
}

// sets charset for DB connection
$db->exec('SET CHARSET utf8');
$db->exec('SET NAMES utf8');

// select parent nodes only
$sql = "SELECT * FROM `daten_grossist_em_table` GROUP BY `Bauform_d` ORDER BY `ArtikelBauformkategorie` ASC;";
$stmt = $db->query($sql);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

// prepare statement for selecting child nodes of specified parent node
$sql = "SELECT * FROM `daten_grossist_em_table` WHERE `Bauform_d` = :cat ORDER BY `ArtikelProduktgruppe_ID` ASC;";
$stmt = $db->prepare($sql);

// create new XML doc
$dom = new DomDocument();
$dom->loadXML('<tree></tree>');

// loop over parents
foreach ($res as $row) {
      $lists_node = $dom->createElement('lists');
      $lists_node->appendChild($dom->createElement('Bauform_d', $row['Bauform_d']));

      $stmt->bindParam(':cat', $row['Bauform_d']);
      $stmt->execute();

      // loop over children
      foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row2) {
            $lists_node->appendChild($dom->createElement('ArtikelProduktgruppe_d', $row2['ArtikelProduktgruppe_d']));
            $lists_node->appendChild($dom->createElement('ArtikelNummer', $row2['ArtikelNummer']));
      }

      $dom->documentElement->appendChild($lists_node);
}

// display XML
header('Content-Type: text/xml');
echo $dom->saveXML();

?>

saad220

ASKER

Please help please???????????

ASKER CERTIFIED SOLUTION

saad220

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial