Beste manier van loopen (Opgelost)

<?php
header('Content-Type: text/html; charset=UTF-8');

// debug
error_reporting(E_ALL);
ini_set('display_errors', 'stdout');

// database data
/*
CREATE TABLE mytrees (
    mtr_id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mtr_name    VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE mytree_items (
    mti_id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mti_tree_id     INT UNSIGNED NOT NULL,
    mti_parent_id   INT UNSIGNED,
    mti_order       INT UNSIGNED NOT NULL,
    mti_title       VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE mytree_items ADD FOREIGN KEY (mti_tree_id) REFERENCES mytrees(mtr_id) ON DELETE CASCADE;
ALTER TABLE mytree_items ADD FOREIGN KEY (mti_parent_id) REFERENCES mytree_items(mti_id) ON DELETE CASCADE;

INSERT INTO mytrees(mtr_name) VALUES ('test');
SET FOREIGN_KEY_CHECKS = off;
INSERT INTO mytree_items(mti_id, mti_tree_id, mti_parent_id, mti_order, mti_title) VALUES
( 1, 1, NULL, 1, 'Hoofdcategorie 1'),
( 2, 1, NULL, 2, 'Hoofdcategorie 2'),
( 3, 1, NULL, 3, 'Hoofdcategorie 3'),
( 4, 1,    1, 1, 'Item 1'),
( 5, 1,    2, 1, 'Subcategorie 1'),
( 6, 1,    2, 2, 'Subcategorie 2'),
( 7, 1,    5, 1, 'Item 1'),
( 8, 1,    5, 2, 'Item 2'),
( 9, 1, NULL, 4, 'Hoofdcategorie 4'),
(10, 1,    9, 1, 'Item 1'),
(11, 1,    9, 2, 'Item 2'),
(12, 1,    9, 3, 'Item 3');
SET FOREIGN_KEY_CHECKS = on;
*/

// class definition
class MyTree
{
    protected $db;
    protected $treeId;
    protected $treeData;
    protected $treeList;

    public function __construct($db) {
        $this->db = $db;
        $this->treeId = false;
        $this->treeName = false;
        $this->treeData = array();
    }

    public function loadTree($id) {
        $this->treeId = false;
        $this->treeData = array();

        $res = $this->db->query(
            'SELECT mtr_name
            FROM mytrees
            WHERE mtr_id = '.$id
        );
        if ($res->num_rows == 1) {
            $row = $res->fetch_assoc();
            $this->treeId = $id;
            $this->treeName = $row['mtr_name'];
            $this->treeData[0] = array(
                // structure data
                'id'        => 0,
                'parent'    => false,
                'depth'     => 0,
                'order'     => 1,
                'children'  => array(),
                // leaf data
                'title'     => 'root',
            );

            $res = $this->db->query(
                'SELECT *
                FROM mytree_items
                WHERE mti_tree_id = '.$this->treeId.'
                ORDER BY mti_parent_id, mti_order'
            );

            while ($row = $res->fetch_assoc()) {
                $parentId = $row['mti_parent_id'] == NULL ? 0 : $row['mti_parent_id'];
                $this->treeData[$row['mti_id']] = array(
                    'id'        => $row['mti_id'],
                    'parent'    => $parentId,
                    'depth'     => ($this->treeData[$parentId]['depth'] + 1),
                    'order'     => $row['mti_order'],
                    'children'  => array(),
                    'title'     => $row['mti_title'],
                );
                // add item as child of parent
                $this->treeData[$parentId]['children'][] = $row['mti_id'];
            }
            return true;
        }
        return false; // alternative: throw exception
    }

    public function getData() {
        return $this->treeData;
    }

    // create a linear list of the entire current tree, or part of it
    protected function makeList($id=0, $reset=true) {
        if ($reset) {
            $this->treeList = array();
        }
        // skip root
        if ($id > 0) {
            $this->treeList[] = $id;
        }
        foreach ($this->treeData[$id]['children'] as $child) {
            $this->makeList($child, false);
        }
    }

    // retrieve the list after creating it
    public function getList($id=0) {
        $this->makeList($id);
        return $this->treeList;
    }

    public function printTree($id=0) {
        if ($id > 0) {
            ?><li><?php
                echo escape($this->treeData[$id]['title']);
        }
        if (count($this->treeData[$id]['children']) > 0) {
            ?><ul><?php
                foreach ($this->treeData[$id]['children'] as $child) {
                    $this->printTree($child);
                }
            ?></ul><?php
        }
        if ($id > 0) {
            ?></li><?php
        }
    }
}

// helper functions
function escape($s) {
    return htmlspecialchars($s, ENT_QUOTES, 'UTF-8');
}

function dump($a) {
    echo '<pre>'.escape(print_r($a, true)).'</pre>';
}
?>
<!DOCTYPE html>
<html>
<meta charset="UTF-8">
<title>tree</title>
</head>

<body>
<?php
$db = new mysqli('host', 'username', 'password', 'database');

$tree = new MyTree($db);
if ($tree->loadTree(1)) {
    $tree->printTree();
} else {
    ?><p>something went wrong :(</p><?php
}
?>
<body>
</html>

<?php
header('Content-Type: text/html; charset=UTF-8');
 
// debug
error_reporting(E_ALL);
ini_set('display_errors', 'stdout');
 
// database data
/*
CREATE TABLE mytrees (
    mtr_id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mtr_name    VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE mytree_items (
    mti_id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mti_tree_id     INT UNSIGNED NOT NULL,
    mti_parent_id   INT UNSIGNED,
    mti_order       INT UNSIGNED NOT NULL,
    mti_title       VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE mytree_items ADD FOREIGN KEY (mti_tree_id) REFERENCES mytrees(mtr_id) ON DELETE CASCADE;
ALTER TABLE mytree_items ADD FOREIGN KEY (mti_parent_id) REFERENCES mytree_items(mti_id) ON DELETE CASCADE;
 
INSERT INTO mytrees(mtr_name) VALUES ('test');
SET FOREIGN_KEY_CHECKS = off;
INSERT INTO mytree_items(mti_id, mti_tree_id, mti_parent_id, mti_order, mti_title) VALUES
( 1, 1, NULL, 1, 'Hoofdcategorie 1'),
( 2, 1, NULL, 2, 'Hoofdcategorie 2'),
( 3, 1, NULL, 3, 'Hoofdcategorie 3'),
( 4, 1,    1, 1, 'Item 1'),
( 5, 1,    2, 1, 'Subcategorie 1'),
( 6, 1,    2, 2, 'Subcategorie 2'),
( 7, 1,    5, 1, 'Item 1'),
( 8, 1,    5, 2, 'Item 2'),
( 9, 1, NULL, 4, 'Hoofdcategorie 4'),
(10, 1,    9, 1, 'Item 1'),
(11, 1,    9, 2, 'Item 2'),
(12, 1,    9, 3, 'Item 3');
SET FOREIGN_KEY_CHECKS = on;
*/
 
// class definition
class MyTree
{
    protected $db;
    protected $treeId;
    protected $treeData;
    protected $treeList;
 
    public function __construct($db) {
        $this->db = $db;
        $this->treeId = false;
        $this->treeName = false;
        $this->treeData = array();
    }
 
    public function loadTree($id) {
        $this->treeId = false;
        $this->treeData = array();
 
        $res = $this->db->query(
            'SELECT mtr_name
            FROM mytrees
            WHERE mtr_id = '.$id
        );
        if ($res->num_rows == 1) {
            $row = $res->fetch_assoc();
            $this->treeId = $id;
            $this->treeName = $row['mtr_name'];
            $this->treeData[0] = array(
                // structure data
                'id'        => 0,
                'parent'    => false,
                'depth'     => 0,
                'order'     => 1,
                'children'  => array(),
                // leaf data
                'title'     => 'root',
            );
 
            $res = $this->db->query(
                'SELECT *
                FROM mytree_items
                WHERE mti_tree_id = '.$this->treeId.'
                ORDER BY mti_parent_id, mti_order'
            );
 
            while ($row = $res->fetch_assoc()) {
                $parentId = $row['mti_parent_id'] == NULL ? 0 : $row['mti_parent_id'];
                $this->treeData[$row['mti_id']] = array(
                    'id'        => $row['mti_id'],
                    'parent'    => $parentId,
                    'depth'     => ($this->treeData[$parentId]['depth'] + 1),
                    'order'     => $row['mti_order'],
                    'children'  => array(),
                    'title'     => $row['mti_title'],
                );
                // add item as child of parent
                $this->treeData[$parentId]['children'][] = $row['mti_id'];
            }
            return true;
        }
        return false; // alternative: throw exception
    }
 
    public function getData() {
        return $this->treeData;
    }
 
    // create a linear list of the entire current tree, or part of it
    protected function makeList($id=0, $reset=true) {
        if ($reset) {
            $this->treeList = array();
        }
        // skip root
        if ($id > 0) {
            $this->treeList[] = $id;
        }
        foreach ($this->treeData[$id]['children'] as $child) {
            $this->makeList($child, false);
        }
    }
 
    // retrieve the list after creating it
    public function getList($id=0) {
        $this->makeList($id);
        return $this->treeList;
    }
 
    public function printTree($id=0) {
        if ($id > 0) {
            ?><li><?php
                echo escape($this->treeData[$id]['title']);
        }
        if (count($this->treeData[$id]['children']) > 0) {
            ?><ul><?php
                foreach ($this->treeData[$id]['children'] as $child) {
                    $this->printTree($child);
                }
            ?></ul><?php
        }
        if ($id > 0) {
            ?></li><?php
        }
    }
}
 
// helper functions
function escape($s) {
    return htmlspecialchars($s, ENT_QUOTES, 'UTF-8');
}
 
function dump($a) {
    echo '<pre>'.escape(print_r($a, true)).'</pre>';
}
?>
<!DOCTYPE html>
<html>
<meta charset="UTF-8">
<title>tree</title>
</head>
 
<body>
<?php
$db = new mysqli('host', 'username', 'password', 'database');
 
$tree = new MyTree($db);
if ($tree->loadTree(1)) {
    $tree->printTree();
} else {
    ?><p>something went wrong :(</p><?php
}
?>
<body>
</html>

<ul>
    <li>Hoofdcategorie 1<ul>
        <li>Item 1</li>
    </ul></li>
    <li>Hoofdcategorie 2<ul>
        <li>Subcategorie 1<ul>
            <li>Item 1</li>
            <li>Item 2</li></ul>
        </li>
        <li>Subcategorie 2</li></ul>
    </li>
    <li>Hoofdcategorie 3</li>
    <li>Hoofdcategorie 4<ul>
        <li>Item 1</li>
        <li>Item 2</li>
        <li>Item 3</li></ul>
    </li>
</ul>

<ul>
    <li>Hoofdcategorie 1<ul>
        <li>Item 1</li>
    </ul></li>
    <li>Hoofdcategorie 2<ul>
        <li>Subcategorie 1<ul>
            <li>Item 1</li>
            <li>Item 2</li></ul>
        </li>
        <li>Subcategorie 2</li></ul>
    </li>
    <li>Hoofdcategorie 3</li>
    <li>Hoofdcategorie 4<ul>
        <li>Item 1</li>
        <li>Item 2</li>
        <li>Item 3</li></ul>
    </li>
</ul>