PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 2

Making Use Of Class NestedTree

The final thing to be done now is to use the class! Earlier in the article, I made a point of saying the class is not used for inserting or deleting of data from the tree table, in order to keep it as reusable as possible.

In the examples below I will provide SQL code for inserting data into the table. You can substitute these references for your own methods of inserting data into your database table.

Some data to work with

In the first article, the sample nested tree we looked at looked like this:

Listing 15 listing-15.txt
Title
------------------------
General Resources
   Code Paste
   Documentation
   Books & Publications
      Apache
      PostgreSQL
      MySQL
   Links
      Databases
      Generators
      Portals

To build this into our tree, we just need to insert it into the table, then create the tree object and run the rebuild() method to setup the tree data.

Here is the SQL to get started, including the table schema and the initial data. Initially we’ll insert the nleft/nright/nlevel data all as 0, because this will be calculated shortly afterwards.

The other thing to note is that because we’re using a sequence for our ID, and manually inserting the IDs, the final step we do is tell PostgreSQL the next sequence value (this way if you insert a new row, the sequence will still behave properly).

Listing 16 listing-16.sql
create table nested_tree (
    id          serial          not null,
    parent_id   int             not null,
 
    title       varchar(255)    not null,
    body        text            not null,
 
    nleft       int             not null,
    nright      int             not null,
    nlevel      int             not null,
 
    primary key (id)
);
create index nested_tree_parent_id on nested_tree (parent_id);
create index nested_tree_nleft on nested_tree (nleft);
create index nested_tree_nright on nested_tree (nright);
create index nested_tree_nlevel on nested_tree (nlevel);
 
insert into nested_tree values ( 1, 0, 'General Resources', '', 0, 0, 0);
insert into nested_tree values ( 2, 1, 'Code Paste', '', 0, 0, 0);
insert into nested_tree values ( 3, 1, 'Documentation', '', 0, 0, 0);
insert into nested_tree values ( 4, 1, 'Books & Publications', '', 0, 0, 0);
insert into nested_tree values ( 5, 4, 'Apache', '', 0, 0, 0);
insert into nested_tree values ( 6, 4, 'PostgreSQL', '', 0, 0, 0);
insert into nested_tree values ( 7, 4, 'MySQL', '', 0, 0, 0);
insert into nested_tree values ( 8, 1, 'Links', '', 0, 0, 0);
insert into nested_tree values ( 9, 8, 'Databases', '', 0, 0, 0);
insert into nested_tree values (10, 8, 'Generators', '', 0, 0, 0);
insert into nested_tree values (11, 8, 'Portals', '', 0, 0, 0);
 
select setval('nested_tree_id_seq', 11, true);

Now we check our tree data:

Listing 17 listing-17.sql
# select * from nested_tree;
 id | parent_id |        title         | body | nleft | nright | nlevel
----+-----------+----------------------+------+-------+--------+--------
  1 |         0 | General Resources    |      |     0 |      0 |      0
  2 |         1 | Code Paste           |      |     0 |      0 |      0
  3 |         1 | Documentation        |      |     0 |      0 |      0
  4 |         1 | Books & Publications |      |     0 |      0 |      0
  5 |         4 | Apache               |      |     0 |      0 |      0
  6 |         4 | PostgreSQL           |      |     0 |      0 |      0
  7 |         4 | MySQL                |      |     0 |      0 |      0
  8 |         1 | Links                |      |     0 |      0 |      0
  9 |         8 | Databases            |      |     0 |      0 |      0
 10 |         8 | Generators           |      |     0 |      0 |      0
 11 |         8 | Portals              |      |     0 |      0 |      0
(11 rows)

In This Article