NYCPHP Meetup

[Adam Fields]

On Wed, Oct 16, 2002 at 03:09:17PM -0400, Jim Hendricks wrote:
> I'm thinking now along the lines of adding a tierlevel field to my table 
> design.  In that way I can do 1 query for each tier. Like:
> 
> // get top tier
> $oRS1 = mysql_query("SELECT ID, Name FROM Projects WHERE TierLevel = 1 
> ORDER BY Name") ;
> // get second tier
> $oRS2 = mysql_query("SELECT ParentID, ID, Name FROM Projects WHERE 
> TierLevel = 2 ORDER BY ParentID, Name") ;
> // get third tier
> $oRS2 = mysql_query("SELECT ParentID, ID, Name FROM Projects WHERE 
> TierLevel = 3 ORDER BY ParentID, Name") ;
> // get fourth tier
> $oRS2 = mysql_query("SELECT ParentID, ID, Name FROM Projects WHERE 
> TierLevel = 4 ORDER BY ParentID, Name") ;
> 
> Now as I iterate through $oRS1, display Name, I then look at $oRS2, if 
> oRS2.ParentID == oRS1.ID, then dip down into iterating $oRS2 until the 
> ParentID == something other than oRS1.ID.  At each level the logic would 
> be the same.

I'd wrap a class around the whole thing. Use that class to broker
reads and writes to the tree table. Add a "changed" timestamp to the
tree, and have a read from the class only reload the tree from the
database if it's changed since the last read. Stash the object in the
session for reuse, or serialize it to another table (or file, or
whatever) for quick retrieval.

> This would require 1 query per level, and it would require choosing a 
> maximum tree depth at design time.  The code might even be simple enough 
> to write through a recursive algorithm, although I have yet to attempt 
> any recursing in PHP so I'm ignorant if this would be the easiest and 
> cleanest way to do it.
> 
> Figuring out the level at the time a Project is saved is simple since I 
> have a parent ID, I can walk the tree backwards until I get a 0 ParentID 
> ( 0 is not a legal ProjectID in my design ).  The number of queries I 
> had to do plus one would be the level of this new item.

You don't even have to do that. You can just take the level of the
parent node + 1.

> What think ye all?



-- 
				- 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