Automating Nested Set Numbering in Database Table

[Chavista]

I’d like to try using a so-called nested set query as described @ http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ with a database table focusing on the animal kingdom. The data is arranged in a parent-child relationship, like this:

TAXON | PARENT
Mammalia | Chordata
Carnivora | Mammalia
Felidae | Carnivora

The problem is that populating two columns (Left and Right) for something as big as the animal kingdom would be virtually impossible. Even if I spent three months doing it, I’d have to change it every time a new species is discovered or a taxon’s classification changes.

So I wondered if anyone has developed a way of automating the process. For example, are you aware of some PHP script that would allow me to query the database and display it, with the query adding two columns (Left and Right) filled with the proper values? I could then save it as a CSV file and import it back into my database table.

Thanks.

[megazoid]

Here is class that I used to manipulate nested sets: github
You can create simple admin panel for your categories using this class.
It handles all the tree data automatically.

Usage example:

$nestedSets = new cmsNestedSets();
$nestedSets->setTable($table_name);

// Add node
$cat_id = $nestedSets->addNode($parent_id);
// Move node
$nestedSets->moveNode($cat_id, $new_parent_id);
// Delete node
$nestedSets->deleteNode($cat_id);

There is also methods to retrieve nodes, but I prefer to use custom queries filtered by left/right keys.

PS: A bit of copyright - this class is based on the code originally written by Joe Celko.

[Chavista]

Wow, thanks. I’ll play around with that and see if I can make it work with my animal tables.

[megazoid]

Note that you may have to change field names in the costructor of the class:

   $this->FieldID = "id";
   $this->FieldIDParent = "parent_id";
   $this->FieldOrder = "ordering";
   $this->FieldLeft = "ns_left";
   $this->FieldRight = "ns_right";
   $this->FieldDiffer = "ns_differ";
   $this->FieldLevel = "ns_level";
   $this->FieldIgnore = "ns_ignore";

They must be the same as in your table