On PHPBuilder.com today there's a new tutorial showing you a way to work with hierarchical data in MySQL from your PHP application in two different ways (methods).

The hierarchical data structure is one of the most common data structures in programming. It represents a set of data where each item (except the root) has one parent node and one or more children nodes. Web programmers use hierarchical data for a number of applications, including content management systems, forum threads, mailing lists, and e-commerce product categorization. In this tutorial , I will explain how to manage hierarchical data using MySQL and PHP. Specifically, I will explore the two most popular hierarchical data models: adjacency list model and nested set model.

They start with the adjacency list model, showing how to do a left join on the table on the parent ID to record ID and then filter through them, merging them into a main array as needed. The nested set model is slightly different and involves two new columns, a "lft" and "rgt", that point you to the records around the current one. This method makes the actual SQL query to find the structure a bit simpler, but isn't quite as flexible as the first method.

On the Zend Developer Zone today there's a new tutorial looking at a database class/component that implements the Nested Set idea for the Zend Framework - NestedSetDbTable.

There are several ways of realizing demand of storing hierarchical data in database, and most popular methods today are Adjacency List and Nested Set models. [...] With Nested Set model [the] whole tree can be retrieved with a single query, but there are lot of things that need to be done when you need to make some changes in hierarchy. From the title of this article, it's obvious which model is my favorite.

The class helps you to manage nested sets of data pulled from a database and extends the Zend_Db_Table component to pull in all of its additional functionality. You define the "left" and "right" columns in the table and can use constants like NEXT_SIBLING and LAST_CHILD to perform inserts and updates without having to worry about a record's place in the set. Code snippets are included to show you how it all works together.

On his CodeUtopia blog Jani Hartikainen gives an inside look at a feature of Doctrine, nested sets.

The Doctrine library comes with a feature called nested set, which makes saving trees in a database easy. However, it's quite easy to accidentally cause a lot of extra unneeded queries if not being careful. Here are some pointers to keep in mind while working with the nested set, and some example queries to make understanding it easier.

He gives an example, showing how to get rows from the database - parent and child - and some optimization tips to keep things light. There's also some pros and cons included for doing it either way (the standard fetching or using the more optimized versions).

Ivan Iordanov has posted a method he's come up with to extend the existing Zend_Db_Table functionality (of the Zend Framework) to create NestedSet models for your app.

Last days I've been trying to customize Zend's Framework. My goal is to create simpler models. Currently I'm making some db models that handle stuff around wide used designs. Such design is the Nested Set model. It is an adjacent list realized in SQL as a tree. You can find a good introduction to nested sets at mysql developer zone.

He steps through the creation of some example database tables, his class and controller to hook into the Zend Framework application and this full code for his interface class to make the NestedSet models easy.