Baumzusammenstellung mit Hilfe von SQL (MySQL)

[omata]

Hier mal ein Beispiel für PHP...

Austauschbare Datenbankschicht (hier für MySQL)...

Code:

class ClassDBMySQL
{
  private $db;
  private $dbHOST;
  private $dbDATA;
  private $dbUSER;
  private $dbPASS;
  private $QUERY;

  public function __construct($dbHOST, $dbDATA, $dbUSER, $dbPASS) {
    $this->db = NULL;
    $this->dbHOST = $dbHOST;
    $this->dbDATA = $dbDATA;
    $this->dbUSER = $dbUSER;
    $this->dbPASS = $dbPASS;
    $this->QUERY = NULL;
  }

  public function Connect($database = '') {
    $this->db = mysql_connect($this->dbHOST, $this->dbUSER, $this->dbPASS);
    if ($this->db) {
      return $this->Select($database);
    } else {
      return false;
    }
  }

  public function Select($database = '') {
    if ($database == '') {
      $database = $this->dbDATA;
    }
    return mysql_select_db($database, $this->db);
  }

  public function Disconnect() {
    if ($this->db) {
      mysql_close($this->db);
    }
  }

  public function Error() {
    return mysql_error();
  }

  protected function OpenSQL($SQL) {
    if ($this->db) {
      return mysql_query($SQL, $this->db);
    }
  }

  protected function FreeSQL($Query) {
    if ($Query) {
      mysql_free_result($Query);
    }
  }

  protected function FetchSQL($Query) {
    if ($Query) {
      return mysql_fetch_array($Query);
    }
  }

}

Klasse zum Einlesen der Baumstruktur in ein Array...

Code:

class ClassDBTree
  extends ClassDBMySQL
{
  private $DATA;
  private $EOF;

  public function __construct($dbHOST, $dbDATA, $dbUSER, $dbPASS) {
    parent::__construct($dbHOST, $dbDATA, $dbUSER, $dbPASS);
    $this->DATA = NULL;
    $this->EOF = false;
  }

  private function SetNext() {
    if ($this->DATA = $this->FetchSQL($this->Query)) {
      $this->EOF = false;
    } else {
      $this->EOF = true;
    }
  }

  private function GetData() {
    return $this->DATA;
  }

  private function fillArray(&$Node, $Level) {
    $abbruch = false;
    while (!$this->EOF && !$abbruch) {
      $data = $this->GetData();
      $id = $data['id'];
      $Node[$id]['data'] = $data;
      $this->SetNext();
      $data = $this->GetData();
      if ($data['Depth'] > $Level) {
        $this->fillArray($Node[$id]['childs'], $Level+1);
      }
      if ($data['Depth'] < $Level) {
        $abbruch = true;
      }
    }
  }

  public function GetArray($SQL) {
    $result = array();
    $this->DATA = NULL;
    $this->EOF = false;
    $this->Query = $this->OpenSQL($SQL);
    $this->SetNext();
    $data = $this->GetData();
    $this->fillArray($result, $data['Depth']);
    $this->FreeSQL($this->Query);
    return $result;
  }

}

Aufruf...

Code:

$Tree = new ClassDBTree('localhost', 'database', 'username', 'password');
if ($Tree->Connect()) {
  $Nodes = $Tree->GetArray("CALL proc_GetNodes(NULL)");
  $Tree->Disconnect();
  echo "<pre>";
  print_r($Nodes);
  echo "</pre>";
}

Ergebnis...

Code:

Array
(
    [1] => Array
        (
            [data] => Array
                (
                    [0] => 1
                    [ID] => 1
                    [1] => A
                    [bezeichnung] => A
                    [2] => 1
                    [Depth] => 1
                    [3] => 1
                    [NodePath] => 1
                )

            [childs] => Array
                (
                    [2] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 2
                                    [ID] => 2
                                    [1] => AA
                                    [bezeichnung] => AA
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|2
                                    [NodePath] => 1|2
                                )

                        )

                    [3] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 3
                                    [ID] => 3
                                    [1] => AB
                                    [bezeichnung] => AB
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|3
                                    [NodePath] => 1|3
                                )

                            [childs] => Array
                                (
                                    [9] => Array
                                        (
                                            [data] => Array
                                                (
                                                    [0] => 9
                                                    [ID] => 9
                                                    [1] => ABC
                                                    [bezeichnung] => ABC
                                                    [2] => 3
                                                    [Depth] => 3
                                                    [3] => 1|3|9
                                                    [NodePath] => 1|3|9
                                                )

                                        )

                                )

                        )

                    [7] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 7
                                    [ID] => 7
                                    [1] => AC
                                    [bezeichnung] => AC
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|7
                                    [NodePath] => 1|7
                                )

                            [childs] => Array
                                (
                                    [8] => Array
                                        (
                                            [data] => Array
                                                (
                                                    [0] => 8
                                                    [ID] => 8
                                                    [1] => ACA
                                                    [bezeichnung] => ACA
                                                    [2] => 3
                                                    [Depth] => 3
                                                    [3] => 1|7|8
                                                    [NodePath] => 1|7|8
                                                )

                                        )

                                )

                        )

                    [4] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 4
                                    [ID] => 4
                                    [1] => B
                                    [bezeichnung] => B
                                    [2] => 1
                                    [Depth] => 1
                                    [3] => 4
                                    [NodePath] => 4
                                )

                        )

                    [5] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 5
                                    [ID] => 5
                                    [1] => BA
                                    [bezeichnung] => BA
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 4|5
                                    [NodePath] => 4|5
                                )

                        )

                    [6] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 6
                                    [ID] => 6
                                    [1] => BB
                                    [bezeichnung] => BB
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 4|6
                                    [NodePath] => 4|6
                                )

                        )

                )

        )

)

Gruss
Thorsten