Sum af mappe hierarki, child/parent

CREATE TABLE IF NOT EXISTS `x_Folder` (
`FolderID` int(11) NOT NULL,
  `FolderParentID` int(11) DEFAULT NULL,
  `FolderNumber` varchar(65) COLLATE utf8_danish_ci NOT NULL,
  `FolderName` varchar(125) COLLATE utf8_danish_ci NOT NULL,
  `FolderDescription` text COLLATE utf8_danish_ci NOT NULL,
  `FolderQuantity` double(11,2) NOT NULL,
  `FolderUnit` varchar(5) COLLATE utf8_danish_ci NOT NULL,
  `FolderPrecision` double(11,2) NOT NULL,
  `FolderPriority` int(11) DEFAULT NULL,
  `FolderProjectID` int(11) NOT NULL,
  `FolderCompanyID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Data dump for tabellen `x_Folder`
--

INSERT INTO `x_Folder` (`FolderID`, `FolderParentID`, `FolderNumber`, `FolderName`, `FolderDescription`, `FolderQuantity`, `FolderUnit`, `FolderPrecision`, `FolderPriority`, `FolderProjectID`, `FolderCompanyID`) VALUES
(1, NULL, '00', 'Mappe 1', 'Dette er den første mappe i projektet', 1.00, 'STK', 0.10, NULL, 1, 1000),
(2, NULL, '01', 'Mappe 2', 'Dette er mappe 2, som er et barn til den første mappe', 1.00, 'STK', 0.10, NULL, 1, 1000),
(3, 2, '02', 'Mappe 3', 'Dette er mappe 3, som er et barn til mappe 2', 1.00, 'STK', 0.10, NULL, 1, 1000),
(4, 2, '04', 'Mappe 4', 'Dette er mappe 4, som er et barn til mappe 2. Denne skulle gerne stå under mappe 2.', 1.00, 'STK', 0.10, 100, 1, 1000),
(5, 1, '05', 'Mappe 5', 'Dette er mappe 5, som er et barn til mappe 1', 1.00, 'STK', 0.10, NULL, 1, 1000),
(6, 2, '06', 'Mappe 6', 'Denne mappe er en undermappe til mappe 5', 1.00, 'STK', 0.00, NULL, 1, 1000),
(7, 2, '06', 'Mappe 6', 'Denne mappe er en undermappe til mappe 5', 1.00, 'STK', 0.10, 10, 1, 1000),
(8, 7, '08', 'Undermappe 8', 'Dette er en undermappe til mappe 7', 1.00, 'STK', 0.10, NULL, 1, 1000),
(9, 5, '09', 'Mappe 8', 'Dette er mapppe 8', 10.00, 'STK', 0.10, NULL, 1, 1000),
(10, NULL, 'Mappe 2', 'Dette er en ny hovedmappe', 'Dette er en ny hovedmappe', 1.00, 'STK', 0.10, NULL, 1, 1000);

CREATE TABLE IF NOT EXISTS `x_FolderLine` (
`FolderLineID` int(11) NOT NULL,
  `FolderLineType` varchar(12) COLLATE utf8_danish_ci NOT NULL,
  `FolderLineNumber` varchar(35) COLLATE utf8_danish_ci NOT NULL,
  `FolderLineQuantity` double(11,3) NOT NULL,
  `FolderLineTotal` double(11,3) NOT NULL,
  `FolderLinePriority` int(11) NOT NULL,
  `CompanyID` int(11) NOT NULL,
  `ProjectID` int(11) NOT NULL,
  `FolderID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Data dump for tabellen `x_FolderLine`
--

INSERT INTO `x_FolderLine` (`FolderLineID`, `FolderLineType`, `FolderLineNumber`, `FolderLineQuantity`, `FolderLineTotal`, `FolderLinePriority`, `CompanyID`, `ProjectID`, `FolderID`) VALUES
(1, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 1),
(2, 'PRODUCT', '094092', 15.000, 1.000, 0, 1000, 1, 6),
(3, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 6),
(4, 'PRODUCT', '094092', 10.000, 7.000, 0, 1000, 1, 3),
(5, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 8),
(6, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 9);

CREATE TABLE dirs (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    parent INTEGER,
    PRIMARY KEY (id)
);

CREATE TABLE files (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    size INTEGER NOT NULL,
    dir INTEGER NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO dirs VALUES (1, 'C:\\', NULL);
INSERT INTO dirs VALUES (2, 'C:\\adir', 1);
INSERT INTO dirs VALUES (3, 'C:\\bdir', 1);
INSERT INTO dirs VALUES (4, 'C:\\adir\\1dir', 2);
INSERT INTO dirs VALUES (5, 'C:\\adir\\2dir', 2);

INSERT INTO files VALUES (1, 'root.txt', 123, 1);
INSERT INTO files VALUES (2, 'a.txt', 456, 2);
INSERT INTO files VALUES (3, 'b.txt', 789, 3);
INSERT INTO files VALUES (4, 'bbig.txt', 10000, 3);
INSERT INTO files VALUES (5, 'a1.txt', 111, 4);
INSERT INTO files VALUES (6, 'a1big.txt', 11111, 4);
INSERT INTO files VALUES (7, 'a2.txt', 222, 5);
INSERT INTO files VALUES (8, 'a2big.txt', 22222, 5);

<?php
class Dir {
    public $id;
    public $name;
    public $parentid;
    public $immedsize;
    public $subdir;
    public $totalsize;
    public function __construct($id, $name, $parentid, $immedsize) {
        $this->id = $id;
        $this->name = $name;
        $this->parentid = $parentid;
        $this->immedsize = $immedsize;
        $this->subdir = array();
        $this->totalsize = 0;
    }
};

function fixup_subdir($dirlist) {
    foreach($dirlist as $id => $dirobj) {
        if($dirobj->parentid != 0) {
            $dirlist[$dirobj->parentid]->subdir[] = $dirobj;
        }
    }
}

function calculate_totalsize($dir) {
    $res = $dir->immedsize;
    foreach($dir->subdir as $subdir) {
        $res += calculate_totalsize($subdir);
    }
    return $res;
    /*
    // use this version for serious use:
    if($dir->totalsize == 0) {
        $dir->totalsize = $dir->immedsize;
        foreach($dir->subdir as $subdir) {
            $dir->totalsize += calculate_totalsize($subdir);
        }
    }
    return $dir->totalsize;
    */
}

function fixup_totalsize($dirlist) {
    foreach($dirlist as $id => $dirobj) {
        $dirobj->totalsize = calculate_totalsize($dirobj);
    }
}

function load() {
    $dirlist = array();
    $con = new mysqli('localhost', 'root', '', 'Test');
    $stmt = $con->prepare('SELECT dirs.id,dirs.name,dirs.parent,SUM(size) AS sumsize FROM dirs LEFT JOIN files ON dirs.id=files.dir GROUP BY dirs.id,dirs.name,dirs.parent;');
    $stmt->execute();
    $stmt->bind_result($id, $name, $parentid, $immedsize);
    while($stmt->fetch()) {
        $dirlist[$id] = new Dir($id, $name, $parentid, $immedsize);
    }
    $stmt->close();
    $con->close();
    fixup_subdir($dirlist);
    fixup_totalsize($dirlist);
    return $dirlist;
}

$dirlist = load();

foreach($dirlist as $id => $dirobj) {
    echo sprintf("%-20s %8d\r\n", $dirobj->name, $dirobj->totalsize);
}

?>