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

Querying Our Tree Data

Now that we have the tree data in the database, the first thing we need to do is rebuild the tree.

To do this, we use our tree class, and run the rebuild() method. Remember you’ll initially need to connect to your database server if you haven’t already.

Listing 18 listing-18.php
<?php
    require_once('NestedTree.class.php');
 
    pg_connect($connectString);
 
    $tree = new NestedTree('nested_tree', 'id', 'parent_id', 'title');
    $tree->rebuild();
?>

Now if you manually query your database, the structure should have the nleft/nright/nlevel values filled out.

Listing 19 listing-19.txt
# select * from nested_tree order by nleft;
 id | parent_id |        title         | body | nleft | nright | nlevel
----+-----------+----------------------+------+-------+--------+--------
  1 |         0 | General Resources    |      |     1 |     22 |      1
  4 |         1 | Books & Publications |      |     2 |      9 |      2
  5 |         4 | Apache               |      |     3 |      4 |      3
  7 |         4 | MySQL                |      |     5 |      6 |      3
  6 |         4 | PostgreSQL           |      |     7 |      8 |      3
  2 |         1 | Code Paste           |      |    10 |     11 |      2
  3 |         1 | Documentation        |      |    12 |     13 |      2
  8 |         1 | Links                |      |    14 |     21 |      2
  9 |         8 | Databases            |      |    15 |     16 |      3
 10 |         8 | Generators           |      |    17 |     18 |      3
 11 |         8 | Portals              |      |    19 |     20 |      3
(11 rows)

Now we continue to fetch the data, assuming the $tree object still exists as we just created it.

Listing 20 listing-20.php
<?php
    $node = $tree->getNode(1);
    print_r($node);
 
    // Prints this:
    //
    // stdClass Object
    // (
    //     [id] => 1
    //     [parent_id] => 0
    //     [title] => General Resources
    //     [nleft] => 1
    //     [nright] => 22
    //     [nlevel] => 1
    // )
?>

All of the methods have been explained in detail earlier in this article so I won’t go over all of them here, but you can see how easy it is to then query the tree data.

The final example here is fetching the path to a node. We’ll fetch the path to the “PostgreSQL” node (which has ID of 6).

Listing 21 listing-21.php
<?php
    $path = $tree->getPath(6, true);
    print_r($path);
 
    // Prints this:
    //
    // Array
    // (
    //     [1] => stdClass Object
    //         (
    //             [id] => 1
    //             [parent_id] => 0
    //             [title] => General Resources
    //             [nleft] => 1
    //             [nright] => 22
    //             [nlevel] => 1
    //         )
    //
    //     [4] => stdClass Object
    //         (
    //             [id] => 4
    //             [parent_id] => 1
    //             [title] => Books & Publications
    //             [nleft] => 2
    //             [nright] => 9
    //             [nlevel] => 2
    //         )
    //
    //     [6] => stdClass Object
    //         (
    //             [id] => 6
    //             [parent_id] => 4
    //             [title] => PostgreSQL
    //             [nleft] => 7
    //             [nright] => 8
    //             [nlevel] => 3
    //         )
    //
    // )
 
?>

When you have this array of the path, you can easily loop over it to build breadcrumbs/trail system.

In This Article