PHP Traversing mySQL Node tree

So basically you want to convert an adjacency list to a nested set? First update the adjacency list (i.e. update the page_parent values ​​to the correct values ​​for your new tree) and then do the transform below.

Using PHP (base code, untested):

class Tree
{    
    private $count = 0;
    private $data = array();

    /**
     * Rebuild nested set
     * 
     * @param $rawData array Raw tree data
     */
    public function rebuild($rawData)
    {
        $this->data = $rawData;
        $this->count = 1;
        $this->traverse(0);        
    }

    private function traverse($id)
    {
        $lft = $this->count;
        $this->count++;

        if (isset($this->data[$id])) {
            $kid = $this->data[$id];
            if ($kid) {
                foreach ($kid as $c) {
                    $this->traverse($c);
                }
            }
        }

        $rgt = $this->count;
        $this->count++;

        // TODO: Update left and right values to $lft & $rgt in your DB for page_id $id
        ...
    }
}

      

        
        
        
      

    

When you call this, $ rawData should contain an array of IDs indexed by the parent ID, you can create it (based on the table structure) like this ($ db should contain an active PDO connection object):

    $sql = 'SELECT page_id, page_parent FROM pages ORDER BY page_parent';

    $stmt = $db->prepare($sql);
    $rawData = array();
    $stmt->execute();
    while ($row = $stmt->fetch()) {
        $parent = $row['page_parent'];
        $child = $row['page_id'];
        if (!array_key_exists($parent, $rawData)) {
            $rawData[$parent] = array();
        }
        $rawData[$parent][] = $child;
    }

      

        
        
        
      

    

To convert, you need something like:

$tree = new Tree();
$tree->rebuild($rawData);

      

        
        
        
      

    

So basically you are creating an array containing all the nodes in the tree, indexed by the parent, which will be recursively traversed to determine the correct left and right values ​​on the node.

BTW You can do this in plain SQL (after you adapt the table and column names): http://bytes.com/topic/mysql/answers/638123-regenerate-nested-set-using-parent_id-structure