Creating a Tree from a self referencing DB table

Today, I had to create a tree structure for companies that had child companies. In the DB, it was stored as a self referencing table, with the top parent having a parent ID of null.
I wanted to display the tree using unordered lists <ul>. It wasn’t as hard as I thought!

I’m making this class a Zend_View_Helper, but if you aren’t using ZF, don’t be put off, the logic is still the same!

First up, I made a DB method in my DB class to get immediate children:

     *  @return Zend_Db_Table_Rowset
    public function getOrganisationsByParent($parent_id)
        $select = $this->select()
                       ->where('parent_id = '.$parent_id)
                       ->order('name ASC');
        return $this->fetchAll($select);

Next, I created my View Helper class. All it does is grab the children, append to the html, and for each child, the method runs itself!


class ESOS_View_Helper_CompanyTree extends Zend_View_Helper_Abstract
    /** @var string $html */
    private $html;
    /** @var ESOS_Repository_Organisation */
    private $org_db;
    public function companyTree($parent_id)
        $this->tree = array();
        $this->org_db = ESOS_Service_Locator::getInstance()->getOrganisationSvc()->getRepository();
        return $this->html;
    public function getChildren($parent)
        $orgs = $this->org_db->getOrganisationsByParent($parent);
        foreach($orgs as $org)
            $this->html .= '<li>'.$org->getName().'<ul>';
            $this->html .= '</ul></li>';

Easy as that! Which produced for me (with limited DB data):

ACME Products Inc.

  • Acme Accounting Solutions
  • Acme Reseach & Development
    • Acme Prototypes Limited

Or if you’d prefer to see the HTML:

<li>ACME Products Inc.
<ul class=”children”>
<li>Acme Accounting Solutions<ul></ul></li>
<li>Acme Reseach &amp; Development
<li>Acme Prototypes Limited<ul></ul></li>


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s