NYCPHP Meetup

[Adam Fields]

On Wed, Oct 16, 2002 at 01:12:23PM -0400, Jim Hendricks wrote:
> 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.

There's no easy way to do this now with MySQL. In Oracle, you can use
a CONNECT BY query to return this sort of structure.

The only good news I have about this is that it is on the list of
things that ought to be included at some point:

http://www.mysql.com/doc/en/TODO_future.html


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