NYCPHP Meetup

[Jim Hendricks(Biz Computing)]

This idea might hold promise for my needs.  I need to mull over.  I 
imagine I can maintain the "subject path" table at file time of  a new 
"subject" since this is a quick save query so tacking on the added time 
to backwalk the tree for the new "subject" to glean the necessary info 
to add to the "subject path" table would be hardly noticed.  I don't 
know how the sorting would be done with this design though.  If I want 
"subject" ordering within the tier, I may need to add a sortation 
weight.  The other thing would be, this design still needs multiple 
queries, for each level of each branch.

I'm thinking that since my requirements are:
   1. a parent can have many children of which can also have many children
   2. a child can have only 1 parent
   3. the tree query needs to display within an alpha sort on name 
within the tier

I might need to have a sort weight field which factors all these in when 
my entity is saved and generates a value which would allow a sort on the 
weight field to cause the query to retrieve the data in the right order 
with parents immediately followed by their children.  Then it is a 
simple matter durring display to do the necessary indenting as you 
interate through the result set.

Michael James wrote:

>Jim,
>
>I had to build a tree for a project, and I found that de-normalization worked rather well.  In my case i had a tree of "subjects", therefore i created a "subject path" table with the following fields:
>
>id                      
>parent_id              
>subject_id_0            
>subject_id_1            
>subject_id_2            
>subject_id_3            
>subject_id_4            
>subject_id_5            
>subject_id_6            
>subject_name_0         
>subject_name_1         
>subject_name_2         
>subject_name_3         
>subject_name_4         
>subject_name_5         
>subject_name_6         
>narrowest_subject_id   
>narrowest_subject_name 
>full_name             
>
>also, i had a subject table:
>id
>name 
>
>
>I knew that I would never go over 7 levels in my tree, but i could always add more fields to the subject path table if necessary.  Anyway, from the subject table and from the id and parent id fields in the subject path table, i can derive all of the other fields for each row in the subject path table.  (this would be something you can do as a pre-processed script, because you wouldn't want to figure it out during run-time since it would slow your site down.)   And I used these de-normalized fields to create the tree on the website using very simple queries (ex. SELECT narrowest_subject_name from subject_path where parent_id = 5)
>
>Mike
>
>  
>
>>>>jim at bizcomputinginc.com 10/16/02 01:12PM >>>
>>>>        
>>>>
>I know this is a SQL question, but since most apps deal with SQL, I 
>figured I'ld ask here since the app is using PHP and MySQL.
>
>I have a table that establishes an entity that may have a parent from 
>the same table.  I would like to display the data in a tree format where 
>all items with a ParentID of 0 ( no parent ) are listed with children 
>indented under the parent.  The nesting can be infinite, but 
>realistically will only be 2 or 3 levels deep.  I can see how to do this 
>through a whole series of queries, but can see how the performance of 
>such a design could be very poor if there is a lot of items.  Looking 
>for any ideas, even if they would require a table design mod to make the 
>queries simpler.  The current design is a single table with ID, and 
>ParentID and whatever other info needed for the item.
>
>Thanks, Jim
>
>
>
>
>
>
>
>
>
>
>
>
>
>--- Unsubscribe at http://nyphp.org/list ---
>
>
>
>
>
>  
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20021016/c43b4bf0/attachment.html>