I thought I would post back

pr0gr4mm3r — Wed, 18 Jun 2008 03:25:20 +0000

I thought I would post back with what I ended up doing in case people were following.

I mostly followed that Sitepoint article that Matt posted except for a couple things.

1) I added an extra field in the database for the page depth, which is used to know how much to indent the page when creating a list. The article had some complex & confusing algorithm for finding it, but I figured that it doesn't change dynamically unless we edit a page, so why not store it in the table? It saves CPU & processing time when forming a page list.

2) I didn't implement the way of adding/deleting pages w/o reforming the entire tree...because I'm lazy. Calling the rebuild_tree() function when I create/delete pages is not a big deal. Optimizing the method of showing pages to visitors is way more important.

Here is the final function that I am using.

<?php
    public function rebuild_tree($parent_id = -1, $left = 0, $depth = -1)
    {
        /* the right value of this node is the left value + 1 */
        $right = $left + 1;
        
        /* get all children of this node */
        $query = $this->db->query("SELECT page_id FROM Pages WHERE parent_id = $parent_id ORDER BY created DESC");
        
        foreach ($query->result_array() as $row)
        {
            /* does this page have children? */
            $right = $this->rebuild_tree($row['page_id'], $right, $depth + 1);
        }
        
        /* update this page with the (possibly) new left, right, and depth values */
        $query = $this->db->query("UPDATE Pages SET lft = $left, rgt = $right, depth = $depth WHERE page_id = $parent_id ORDER BY created DESC LIMIT 1");
        
        /* return the right value of this node + 1 */
        return $right + 1;
    }
?>

Pro MySQL. I love it. It

teammatt3 — Thu, 12 Jun 2008 16:07:16 +0000

Pro MySQL. I love it. It makes me seem so smart on these forums . It's one of the only tech books I've read from cover to cover. I learned a ton about MySQL and SQL in general.

That sitepoint article is

pr0gr4mm3r — Thu, 12 Jun 2008 15:56:40 +0000

That sitepoint article is exactly what I need - thanks! I feel better knowing I was on the right track with the PHP solution. The code in that article strongly resembles the function I wrote up.

What is this book you speak of?

I have had the same problems

teammatt3 — Thu, 12 Jun 2008 15:18:00 +0000

I have had the same problems as you. The solution is on the SQL side of things, and it's kind of a pain to do (at least the solution I know about). There was an article on sitepoint about it and there is a lengthy section on it in my Pro MySQL book. I have never implemented it, but it's the only way I know about where recursion on the application code side isn't needed.

The technique is called "nested set model" and you were using the "adjacent list model" and there's another technique called "path enumeration model" to traverse hierarchical data - so says my book