Hjælp til query af nested data.

CREATE TABLE treedemo (
    id INTEGER NOT NULL,
    parentid INTEGER,
    typ VARCHAR(20),
    PRIMARY KEY(id)
);

INSERT INTO treedemo VALUES(1,null,'Folder');
INSERT INTO treedemo VALUES(2,1,'Folder');
INSERT INTO treedemo VALUES(3,2,'Folder');
INSERT INTO treedemo VALUES(4,3,'Folder');
INSERT INTO treedemo VALUES(5,3,'Content');
INSERT INTO treedemo VALUES(6,3,'Folder');
INSERT INTO treedemo VALUES(7,6,'Content');
INSERT INTO treedemo VALUES(8,6,'Content');

<?php
class Record {
    public $id;
    public $parentid;
    public $typ;
    public function __construct($id, $parentid, $typ) {
        $this->id = $id;
        $this->parentid = $parentid;
        $this->typ = $typ;
    }
};

function load_children($currentid, $con, &$list) {
    $stmt = $con->prepare('SELECT id,parentid,typ FROM treedemo WHERE parentid=?');
    $stmt->bind_param('i', $currentid);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($id, $parentid, $typ);
    $folders = array();
    while($stmt->fetch()) {
        if($typ == 'Content') {
            $list[] = new Record($id, $parentid, $typ);
        } else if($typ == 'Folder') {
            $folders[] = $id;
        }
    }
    $stmt->close();
    foreach($folders as $folderid) {
        load_children($folderid, $con, $list);
    }
}

function load($startid) {
    $list = array();
    $con = new mysqli('localhost', 'root', '', 'Test');
    $stmt = $con->prepare('SELECT id,parentid,typ FROM treedemo WHERE id=?');
    $stmt->bind_param('i', $startid);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($id, $parentid, $typ);
    $children = false;
    if($stmt->fetch()) {
        if($typ == 'Content') {
            $list[] = new Record($id, $parentid, $typ);
        } else if($typ == 'Folder') {
            $children = true;
        }
    }
    $stmt->close();
    if($children) {
        load_children($startid, $con, $list);
    }
    $con->close();
    return $list;
}

$all3 = load(3);
foreach($all3 as $one3) {
    echo $one3->id . ' ' . $one3->typ . "<br>\r\n";
}
$all8 = load(8);
foreach($all8 as $one8) {
    echo $one8->id . ' ' . $one8->typ . "<br>\r\n";
}
$all9 = load(9);
foreach($all9 as $one9) {
    echo $one9->id . ' ' . $one9->typ . "<br>\r\n";
}
$t1 = microtime(true);
for($i = 0; $i < 1000; $i++) {
    load(3);
}
$t2 = microtime(true);
echo ($t2 - $t1) . "<br>\r\n";
?>

<?php
class Record {
    public $id;
    public $parentid;
    public $typ;
    public $children;
    public function __construct($id, $parentid, $typ) {
        $this->id = $id;
        $this->parentid = $parentid;
        $this->typ = $typ;
        $this->children = array();
    }
};

function loadall() {
    $list = array();
    $con = new mysqli('localhost', 'root', '', 'Test');
    $stmt = $con->prepare('SELECT id,parentid,typ FROM treedemo');
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($id, $parentid, $typ);
    while($stmt->fetch()) {
        $list[$id] = new Record($id, $parentid, $typ);
        if($parentid != null) $list[$parentid]->children[] = $id;
    }
    $stmt->close();
    $con->close();
    return $list;
}

function load_children($currentid, $all, &$list) {
    $folders = array();
    foreach($all[$currentid]->children as $childid) {
        $child = $all[$childid];
        if($child->typ == 'Content') {
            $list[] = $child;
        } else if($child->typ == 'Folder') {
            $folders[] = $child->id;
        }
    }
    foreach($folders as $folderid) {
        load_children($folderid, $all, $list);
    }
}

function load($startid, $all) {
    $list = array();
    $children = false;
    if(array_key_exists($startid, $all)) {
        $startrec = $all[$startid];
        if($startrec->typ == 'Content') {
            $list[] = $startrec;
        } else if($startrec->typ == 'Folder') {
            $children = true;
        }
    }
    if($children) {
        load_children($startid, $all, $list);
    }
    return $list;
}

$all = loadall();
$all3 = load(3, $all);
foreach($all3 as $one3) {
    echo $one3->id . ' ' . $one3->typ . "<br>\r\n";
}
$all8 = load(8, $all);
foreach($all8 as $one8) {
    echo $one8->id . ' ' . $one8->typ . "<br>\r\n";
}
$all9 = load(9, $all);
foreach($all9 as $one9) {
    echo $one9->id . ' ' . $one9->typ . "<br>\r\n";
}
$t1 = microtime(true);
for($i = 0; $i < 1000; $i++) {
    load(3, $all);
}
$t2 = microtime(true);
echo ($t2 - $t1) . "<br>\r\n";
?>