retrieve id of child folders via parent_id

SeenGee

I have a hierarchical folder structure in mysql, each folder knows the id of its parent folder (parent_id). How can i retrieve all folders that are children or children of children of a specific starting folder?
For example, if i started with a unique id for a folder called c:, how can i retrieve the id of every child folder of c: such as c/documents/images/holidays/spain ?

Thanks.

laserlight

Recursively search for child folders that have the folder in question as their parent.

SeenGee

thanks, but can you give me any guidance on the best way to do this?

laserlight

Not sure about the best myself, and it would depend on your database schema and script requirements anyway.

Here's some very simple example code:

function getChildFolders($id) {
	$result = "SELECT id FROM folders WHERE id=$id";
	$ret = array();
	while ($row = mysql_fetch_array($result)) {
		$ret[] = getChildFolders($row['id']);
	}
	return $ret;
}

SeenGee

i tried this:

function getChildFolders($id) {
    $sql = "SELECT `cf_id` FROM `scc_cabinet_folders` WHERE `cf_parent_id`= '$id'";
    //echo $sql;
	$result = mysql_query($sql) or die(mysql_error());
	$ret = array();
    while ($row = mysql_fetch_array($result)) {
        $ret[] = getChildFolders($row['cf_parent_id']);
    }
    return $ret;
}

getChildFolders(1);

but this script just seems to run in an infinite loop, whats going wrong?

BTW, the id i need is cf_id and the parent id is ... parent_id

Any help would be much appreciated