<!-- Collection of code snippets by Arne Vajhøj -->
<!-- posted to eksperten.dk, usenet and other places (2002-now) -->
<?php
/*
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);

SELECT dirs.name,files.name,size
FROM dirs LEFT JOIN files ON dirs.id=files.dir;

SELECT dirs.name,SUM(size) AS sumsize
FROM dirs LEFT JOIN files ON dirs.id=files.dir
GROUP BY dirs.name;

-- MySQL does not allow recursive UDF
DELIMITER //
CREATE FUNCTION dirsize(_dirid INTEGER) RETURNS INTEGER
BEGIN
    DECLARE _size INTEGER;
    DECLARE _subdirid INTEGER;
    DECLARE _done INTEGER DEFAULT FALSE;
    DECLARE _cur CURSOR FOR SELECT id FROM dirs WHERE parent=_dirid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
    SELECT SUM(size) INTO _size FROM files WHERE dir=_dirid;
    OPEN _cur;
    iterate_loop: LOOP
        FETCH _cur INTO _subdirid;
        IF _done THEN
            LEAVE iterate_loop;
        END IF;
        SET _size = _size + dirsize(_subdirid);
    END LOOP;
    CLOSE _cur;
    RETURN _size;
END//
DELIMITER ;

SELECT name,dirsize(id) AS sumsize
FROM dirs;

DROP FUNCTION dirsize;

DROP TABLE files;
DROP TABLE dirs;

*/
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);
}

?>