CategoryID/ParentCategoryID Looping Help

How do you get away with generating a tab开发者_开发技巧bed list of CategoryID/ParentCategoryID the simplest way? I am no SQL expert, but logically, this is very difficult for me..

Can it be done with 1 or 2 SQL statements, maybe a Do/Loop For/Next would work?

The list should look like this:

Category -Subcategory --SubSubcategory ---InfiniteSubcategories

Category -etc.. --etc...

Read up on thing called "Closure Tables". It might make few things easier for you.

This is certainly not an easy problem(if performance is of any importance), so it might be a good idea to read up on some of the solutions that are scattered on the internet.

For a simple(and not at all well suited for tasks where performance is important) you can do this:

function readChildrenNodes(parentNode) {
    newNodes = sql_db.get("SELECT * FROM nodes WHERE parent="+parentNode.id);
    parentNode.appendChildren(newNodes);
    for node in newNodes
        readChildrenNodes(node);
}