NYCPHP Meetup

[Jim Hendricks(Biz Computing)]

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.

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.

What think ye all?