Folder and subfolders: how to query?

i have got 2 tables which should show a folder structure as a tree. So every folder has an id and a name. if it is a subfolder of an other folder it开发者_JS百科 has an parent id like this:

Table folder:

Folderid name  
11111112 xxy  
11111113 yyy  
11111114 yxy 

...

table Structur

folderid parentid ...  
11111112 NULL  
11111113 11111112  
11111114 11111113  

My question now is how can i check with a mysql query whether a folder is a subfolder of another (but it hasnt to be the parent one)

For example: How can i check whether folder 11111114 is in 11111112??? I would check recursive but the problem is that the count is unkown so i downt now how often to do this step.

maybe u can help me??

The MySQL docs have some guidelines on storing hierarchical data: Managing Hierarchical Data in MySQL.

let's start with this structure (let's name table folders). Maybe you can rework this case to yours.

{
    id     => 'int',
    label  => 'string', # folder name
    parent => 'int'     # reference to folder parent (only one) 
                        # folder has unlimited number of children
}

some more pseudo-code

subtree_ids = select_subtree_ids (root)

if id_folder in subtree_ids
    bingo! id_folder is subfolder of root

what you need is to write select_subtree_ids ()

you can't do this in one query (am I wrong?)

something like this (pseudo-code, again)

select_subtree (root) {

    ids = (root); # result array contains initial id (for convenience)

    while (TRUE) { # depth is unknown

        # array of folder ids from next level only
        my new_ids = sql_select_array ("SELECT id FROM folders WHERE parent IN (ids) AND id NOT IN (ids)");

        break if no_new_ids;

        ids  = ids + new_ids;

    }

    return ids;
}