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>

Logging your Zend Framework 1 App

Just a quick post, but this is a nice way of setting up logging!

I have a class extending Zend_Controller_Action. Instead of my controllers extending Zend_Controller_Action, they now extend my own class. In that class, I added two methods, applicationLog(), and systemLog().

Application log will log user actions in your site, system log will log any exceptions or other stuff. The cool thing is, if you are using Firefox, you can send directly to the console!

In your base controller class:

     *  Log any errors etc
     *  @param string $type
     *  @param string $message
    public function systemLogger($type='info',$message)
        /** @var Zend_Log $log*/
        $log = Zend_Registry::get('logger');
     *  Audit site usage
     *  @param string $type
     *  @param string $message
    public function applicationLogger($type='info',$message)
        /** @var Zend_Log $log*/
        $log = Zend_Registry::get('applog');
        /** @todo */
        // $user = Zend_Registry::get('user');
        // $log->setEventItem('user_id',$user->getId());    
        // $log->setEventItem('user_email',$user->getEmail());    

In your Bootstrap, initialise them like this:

         *  Initialise Logger
        protected function _initLogging()
            $logger = new Zend_Log();
            $logFileDatePart = date('Y-m-d', time());
            switch ( $this->getEnvironment() ) {
                case 'production' :
                case 'staging' :
                    $writer = new Zend_Log_Writer_Stream(
                    APPLICATION_PATH . '/../data/logs/esos_' . $logFileDatePart . '.log');
                    $filter = new Zend_Log_Filter_Priority( Zend_Log::INFO );
                default :
                    $writer = new Zend_Log_Writer_Firebug();
                    $filter = new Zend_Log_Filter_Priority( Zend_Log::DEBUG );
            $logger->addWriter( $writer );
            $logger->addFilter( $filter );
            $this->_logger = $logger;
            Zend_Registry::set('logger', $logger);
         *  Initialise Application Log
        protected function _initApplicationLogging()
            $db = Zend_Db_Table::getDefaultAdapter();
            $mapping = array(
                    'customer_email'    => 'customer_email',
                    'customer_name'        => 'customer_name',
                    'company_name'        => 'company_name',
                    'category'            => 'category',
                    'action'            => 'action',
                    'description'        => 'message',
                    'priority'            => 'priority',
                    'priority_name'        => 'priorityName',
                    'dump'                => 'dump'
            $applog = new Zend_Log();
            $writer = new Zend_Log_Writer_Db( $db, 'applog', $mapping);
            $filter = new Zend_Log_Filter_Priority( Zend_Log::DEBUG );
            $applog->addWriter( $writer );
            $applog->addFilter( $filter );
            Zend_Registry::set('applog', $applog);
            // set user params here to save having to do it each time applog is used
            //    $applog->info('application logging installed');

Only in a development environment will logs be sent to the firebug console. Otherwise, it will go in a log file. Set your path and permissions accordingly. Finally, create your DB table for the application log:

 *    Application Log Table
CREATE TABLE `esos_auditor`.`applog` (
    id                                INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,        -- id of log item
    category                        VARCHAR(30),                -- category of action / activity
    action                            VARCHAR(30),                -- action / activity
    user_id                            INT UNSIGNED,                    -- id of user performing action
    user_email                        VARCHAR(100),                -- email address of user performing action
    action_time                        TIMESTAMP default CURRENT_TIMESTAMP,    -- time action ocurred
    description                        VARCHAR(200),                -- brief description of action
    priority                        TINYINT,                    -- numeric log level
    priority_name                    VARCHAR(20),                -- Zend log level name
    dump                            TEXT,                
    PRIMARY KEY (id)

Now, in your controller (Extending your base class of course), you can call $this->systemLogger(‘err’,$e->getMessage()); in your catch blocks! You can have various levels of logging as per the Zend_Log page:

  1. EMERG   = 0;  // Emergency: system is unusable
  2. ALERT   = 1;  // Alert: action must be taken immediately
  3. CRIT    = 2;  // Critical: critical conditions
  4. ERR     = 3;  // Error: error conditions
  5. WARN    = 4;  // Warning: warning conditions
  6. NOTICE  = 5;  // Notice: normal but significant condition
  7. INFO    = 6;  // Informational: informational messages
  8. DEBUG   = 7;  // Debug: debug messages

Remember and use lower case when passing err or warn etc. Have fun with it!

Using PuPHPet and Vagrant

I have a new job! And it looks like we’ll be using mainly ZF2!

However, in this new workplace there is no Linux box to use as a development server, and so I have been tasked with setting up my development environment. XAMPP was mentioned, but you have to be joking! The best way these days to set up a dev environment (which can be moved onto any machine!) is using Vagrant, and PuPHPet.

First up, install VirtualBox. If you are using Symantex Norton AV horribleness, VirtualBox just wont work in the current release, and so you should download a 4.3.15 build, which I’ll link to for you to save time:

Next up, install Vagrant.

Then, head over to, and go through the setup wizard, where you can choose what your server needs installed.

Finally open VirtualBox, and a command prompt, cd into the folder and type “vagrant up”. A lot of initialisation will scroll past, and hopefully you dont get any errors!

Now you have two options. You can either type ‘vagrant ssh’ to log in to your new machine, or follow the steps below:

Open up Putty Pageant, and import your generated key id_rsa.ppk, which can be found in the puphpet/files/dot/ssh folder.
Open the config.yaml file in the puphpet folder. Open Putty itself, and look for the private_network: key. This is the IP address you will connect to. Then click on the connection data tab and enter vagrant in as the user name. Finally go back into the session tab, give a name to the session and then hit save. Now you can connect up and have a fully working Linux Box which can be moved from machine to machine!

We dont even need to set up a Samba share! PuPHPet has set up a folder that syncs already, so have fun!

Last but not least, I had an issue setting up my SSH tunnel where I couldnt connect my HeidiSQL. Open sudo nano /etc/mysql/my.cnf and change the bind address from to, then restart using ‘sudo service mysql restart’. Job done, ready to rock!

Geolocate your visitors!

Right now I’m building a mobile app using Intel’s AppFramework for a single page site, and Apigility for the API.

In the app, there is an autosuggest for towns and postcodes, however I thought it would be nice to have a “Locate Me” button, able to pinpoint your visitor (hopefully with a decent degree of accuracy!), so I Googled away and found the following:

In your site go into your js folder and clone the repo:

cd js
git clone

In your page you’ll include the scripts and add your functionality as required:

<script src="js/javascript-mobile-desktop-geolocation/js/geoPosition.js" type="text/javascript" charset="utf-8"></script>
 <script src="js/javascript-mobile-desktop-geolocation/js/geoPositionSimulator.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
    // Geolocation Initialisation
    geoPosition.getCurrentPosition(success_callback,error_callback,    {enableHighAccuracy:true});
    // You cannot use Geolocation in this device
// p : geolocation object
 function success_callback(p)
    // p.latitude : latitude value
    // p.longitude : longitude value
function error_callback(p)
    // p.message : error message

Now if you load your page and go into the web inspector, hopefully you should have some coordinates!

The next part of the puzzle is where it gets interesting: How to find the closest match in your table of areas, postcodes, and lat longs!

I would never have figured this out, but there is an equation called the Haversine formula, which uses spherical trigonometry to calculate areas within a certain distance! It looks like this:

R = earths radius (mean radius = 6,371km)
Δlat = lat2 lat1
Δlong = long2 long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1a))
d = R.c
Angles need to be in radians to pass to Trigonometric functions

Here’s my actual query itself:

 ( 3959 * acos( cos( radians(55.864237) ) * cos( radians( latitude ) ) 
 * cos( radians( longitude ) - radians(-4.251806) ) + sin( radians(55.864237) ) 
 * sin( radians( latitude ) ) ) ) AS distance 
FROM postcodes HAVING distance < 20 
ORDER BY distance LIMIT 1;

Here I check for any area within 20 miles, but you can make this as short or long as you want.  The 3959 figure at the start of the query is the number used for miles, if you are using kilometres you should change this number to 6371.  I have limited it to 1 row, as I only want the closest match, however you may want to change this in other situations!

So there we have it! NSA-like spying on your visitors! (With their permission of course!)