Assistance with Putting Together Heirarchy of Projects

[JeffWalden]

I'm trying to put together a project management system built with PHP and MySQL. Included in this CRM will be the ability to make certain projects children of other projects. For instance:

Project 1 (parent of project 2 and project 3)

Project 2 (child of project 1)

Project 3 (child of project 1 and parent of project 4)

Project 4 (child of project 3)

My database currently is very simple in that my "Projects" table has a column for the Parent_Pjt ID number. This I expect to be expanded upon. My real question is how to go about building the query and array to display these projects?

My initial though is to have a while() loop to display the list of the first level of projects. Then inside of that while loop I would have another while loop to display the second level of projects. However, I don't know how many levels deep to build the code as projects should be able to be related as many levels deep as necessary.

Anyone have some simple ideas as to how I might be able to accomplish this?

[poncho]

You will find a great article on the Sitepoint website about storing hierarchial data in a database. Be aware that the method you have chosen (adjacent list) can have performance issues depending on the level of complexity. I have just used this method for an image gallery system and it can be a bit slow at times. I would seriously consider the "Modified Preorder Tree Traversal" if the relationships are going to be any more complex than your example above.

Cheers;
Poncho