NYCPHP Meetup

[Adam Fields]

On Wed, Oct 16, 2002 at 04:16:36PM -0400, Jim Hendricks wrote:
> That's what I'm doing now, but in order to build the whole tree requires 
> multiple queries with this design. My latest idea is to add a tierlevel 
> field so I won't have to issue a query for parentID = 0 ( top level ) 
> then iterate this result & generate a query for parentID = the id of the 
> row we are on in top level etc. right on down the line.  If I have 4 top 
> level projects each with 3 sub projects and 1 of the subprojects have 2 
> subprojects this would require issuing 1 toplevel query, 4 2nd level 
> queries,  12 3rd level queries, and 2 4th level queries.  All told for 
> this simple tree I will have issued 19 queries.  If I have a tierlevel 
>  I can issue 1 query per allowable tier which would be 4 queries in all 
> for a max 4 tierdepth tree.  This would be the same no matter how many 
> items I have in the tree, nor how many children etc.

You can do this without the tierlevel by building dynamic queries of
the form:

(sorry, I forgot your table/column names)

# get the top list first

select node_id, name from tree where parent_id = 0;

# get the results and build list x,y,z of those nodes
# then query for the nodes which have those nodes as parents

select node_id, parent_id, name from tree where parent_id in (x,y,z);

# and so forth.


-- 
				- Adam

-----
Adam Fields, Managing Partner, fields at surgam.net
Surgam, Inc. is a technology consulting firm with strong background in
delivering scalable and robust enterprise web and IT applications.
http://www.adamfields.com