Reply to thread

Message

Okay, so I have a database that is constructed as so:

[CODE]CREATE TABLE Categories (
category_id    integer PRIMARY KEY AUTOINCREMENT,
content_id int,
parent_category_id int,
category_name    varchar(100),
pinned int,
pincode    int
);[/CODE]

That DB will have the names of root and child nodes which will be used to populate a treeview. What will be done is the code will run a query such as this on the DB:

[CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=0;[/CODE]

That will grab all of the category information I need (at this point in time) from the DB. It will basically return all of the 'root nodes' for my category tree.

Now that I have the root nodes, I need to use each of those (referencing the category_id) to go through the DB again to select any child nodes.

[CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=%VAR HERE WITH ID%;[/CODE]

That works fine...it goes ahead and grabs the listing of child nodes for each root node. But, this is where the trick comes into play.

I once again need to repeat the 2nd SQL statement for each of the child nodes returned the first time I ran the 2nd query...then I need to do it again for the 2nd list returned, and then the 3rd, and the 4th, etc. Depending on the complexity of the tree, I could have to do that hundreds of times per each root node.

I am running into a problem there. I can not seem to logically tackle this without nesting hundreds upon hundreds of for/next, or if/then, or while loops.

Can anyone offer some ideas? Perhaps some pseudo code or something to point me in the right direction?

<blockquote data-quote="jmhecker" data-source="post: 540404" data-attributes="member: 86959">Okay, so I have a database that is constructed as so:[CODE]CREATE TABLE Categories (&nbsp; category_id&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; integer PRIMARY KEY AUTOINCREMENT,&nbsp; content_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int,&nbsp; parent_category_id&nbsp; int,&nbsp; category_name&nbsp; &nbsp; &nbsp;&nbsp; varchar(100),&nbsp; pinned&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int,&nbsp; pincode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; int);[/CODE]That DB will have the names of root and child nodes which will be used to populate a treeview.&nbsp; What will be done is the code will run a query such as this on the DB:&nbsp; [CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=0;[/CODE]That will grab all of the category information I need (at this point in time) from the DB.&nbsp; It will basically return all of the 'root nodes' for my category tree.Now that I have the root nodes, I need to use each of those (referencing the category_id) to go through the DB again to select any child nodes.[CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=%VAR HERE WITH ID%;[/CODE]That works fine...it goes ahead and grabs the listing of child nodes for each root node.&nbsp; But, this is where the trick comes into play.I once again need to repeat the 2nd SQL statement for each of the child nodes returned the first time I ran the 2nd query...then I need to do it again for the 2nd list returned, and then the 3rd, and the 4th, etc.&nbsp; Depending on the complexity of the tree, I could have to do that hundreds of times per each root node.I am running into a problem there.&nbsp; I can not seem to logically tackle this without nesting hundreds upon hundreds of for/next, or if/then, or while loops.Can anyone offer some ideas?&nbsp; Perhaps some pseudo code or something to point me in the right direction?</blockquote>

[QUOTE="jmhecker, post: 540404, member: 86959"] Okay, so I have a database that is constructed as so: [CODE]CREATE TABLE Categories ( category_id integer PRIMARY KEY AUTOINCREMENT, content_id int, parent_category_id int, category_name varchar(100), pinned int, pincode int );[/CODE] That DB will have the names of root and child nodes which will be used to populate a treeview. What will be done is the code will run a query such as this on the DB: [CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=0;[/CODE] That will grab all of the category information I need (at this point in time) from the DB. It will basically return all of the 'root nodes' for my category tree. Now that I have the root nodes, I need to use each of those (referencing the category_id) to go through the DB again to select any child nodes. [CODE]SELECT category_id, category_name FROM Categories WHERE parent_category_id=%VAR HERE WITH ID%;[/CODE] That works fine...it goes ahead and grabs the listing of child nodes for each root node. But, this is where the trick comes into play. I once again need to repeat the 2nd SQL statement for each of the child nodes returned the first time I ran the 2nd query...then I need to do it again for the 2nd list returned, and then the 3rd, and the 4th, etc. Depending on the complexity of the tree, I could have to do that hundreds of times per each root node. I am running into a problem there. I can not seem to logically tackle this without nesting hundreds upon hundreds of for/next, or if/then, or while loops. Can anyone offer some ideas? Perhaps some pseudo code or something to point me in the right direction? [/QUOTE]