Logic of recursive function

[eluser]Ninjabear[/eluser]
I've been following this article on recursion in php.

I just can't get my head around the logic of recursion at the moment.

Basically I have a nested tree of projects with infinite levels. So 1 is the root node and then there are others beneath it. Each one has a p_id (primary key) and parent_id to show who its parent is.

This is Codeigniter 2.1.0.

Code:

function recursion($p_id)  
  {
    $projects = $this->get_projects( array('parent_id'=>$p_id) );
  
    if ($projects['num_rows']>0)
    {
        foreach ( $projects['result'] as $k =>$v )
        {
            $children = $this->get_projects( array('parent_id'=>$v->p_id) );
        
            if ( $children['num_rows'] > 0 )
            {
              $array[ $v->p_id ] = $this->recursion( $v->p_id );
            }
            elseif (isset($v->p_id))
            {
              $array[] = $v;
            }
        }
    }
    return (isset($array) ? $array : false);
  }

The get_projects() function just returns a list of projects restricted by certain fields. I don't think the details matter too much except that it ends like this (by all means ask me if it would be helpful):

Code:

...
   $query = $this->db->get();//query
  
     if(isset($options['p_id']))
    $result['result'] = $query->row();
   else
    $result['result'] = $query->result();
    
   $result['num_rows'] = $query->num_rows();

   return $result;
}

So you get either a result or row plus num_rows in an array. get_projects() works throughout the site.

The problem is that wherever there is a child element it doesn't get printed.

Code:

Array
(
    [2] => Array
        (
            [0] => stdClass Object
                (
                    [p_id] => 120
                    [c_id] => 2
                    [project_name] => Another project
                    [project_desc] => another project
                    [date_added] => 2012-01-01 00:00:00
                    [parent_id] => 2
                    [allocated_hrs] => 100
                    [date_from] => 2012-01-01 00:00:00
                    [date_to] => 2012-01-01 00:00:00
                    [status] => active
                    [priority] => 1
                    [completed] => 1
                    [author] => 271
                    [p_date_added] => 1st Jan 2012
                    [date_from_formatted] => 2012-01-01
                    [date_to_formatted] => 2012-01-01
                    [proj_id] => 120
                    [cust_name] => Customer 2
                    [cust_id] => 2
                )

        )

    [3] => stdClass Object
        (
            [p_id] => 3
            [c_id] => 2
            [project_name] => This is a project
            [project_desc] => This is a projectThis is a projectThis is a projectThis is a projectThis is a projectThis is a projectThis is a projectThis is a projectThis is a project
            [date_added] => 2012-03-28 09:27:16
            [parent_id] => 1
            [allocated_hrs] => 100
            [date_from] => 2012-10-10 00:00:00
            [date_to] => 2012-11-10 00:00:00
            [status] => active
            [priority] => 1
            [completed] => 0
            [author] => 271
            [p_date_added] => 28th Mar 2012
            [date_from_formatted] => 2012-10-10
            [date_to_formatted] => 2012-11-10
            [proj_id] => 3
            [cust_name] => Customer 2
            [cust_id] => 2
        )

)

Notice here that p_id 2 isn't there but has child elements.

[eluser]gRoberts[/eluser]
I'd advise against querying the database in a loop or using recursion. Your best bet, is to pull ALL of the rows back and then use PHP to recurs the list and build a tree.

Code:

<?

public function index()
{
  $projects = $this->Recurs($this->get_projects());
  var_dump($projects);
}

private function get_projects($options = array())
{
  return array(); // obviously you return your full list of projects here.
}

private function Recurs($Projects, $ParentID = null)
{
  $Ret = array();
  foreach($Projects as $Project)
  {
   if($Project->p_id != $ParentID) // skip those rows not related to the parent
    continue;
   $Project->Children = $this->Recurs($Projects, $Project->c_id); // find any child rows related to this project
   $Ret[$Project->c_id] = $Project; // add the project to the
  }
  return $Ret;
}

?>

This is hand typed and untested, so I assume it may need some tweaks, but it "should" work.

Basically, each time you call recurs, you pass the entire list of projects and a parent id. Recurs assumes that in your database, the "root" level has a p_id of null, but you can change this to -1 or what ever you like.

It then loops through the first level, finds all the child projects of the root level and each time it finds a project, it then calls recurs to get the projects child projects and so on.

Hopefully, you should end up with a tree of projects, each project will have a Children property that will contain (if available) a list of it's child projects.

hope that helps?

[eluser]Ninjabear[/eluser]
[quote author="gRoberts" date="1333096618"]
hope that helps?[/quote]

Yes it does, thanks for the code. Unfortunately my ftp just went down for who knows how long so I can't test it, but will have a go later.

[eluser]gRoberts[/eluser]
Out of interest, you don't have web hosting with Fasthosts do you? The whole place seems to be falling apart at the moment including ALL of my clients email access

[eluser]Ninjabear[/eluser]
[quote author="gRoberts" date="1333101267"]Out of interest, you don't have web hosting with Fasthosts do you? The whole place seems to be falling apart at the moment including ALL of my clients email access [/quote]

No, I'm with Eukhost. I've used Fasthosts in the past and found them expensive i.e. charging a price per mysql db but not unreliable as such.

Eukhost suffered a DDOS attack yesterday on our shared server so couldn't do anything.

[eluser]gRoberts[/eluser]
Ah gotcha, odd that Fasthosts was down yesterday too huh

Anyhoo, hope you get sorted with your issue.

Cheers

[eluser]Ninjabear[/eluser]
[quote author="gRoberts" date="1333197650"]Ah gotcha, odd that Fasthosts was down yesterday too huh

Anyhoo, hope you get sorted with your issue.

Cheers[/quote]

I always look for a server updates page. I think I found your email issue here:

System status

[eluser]Ninjabear[/eluser]
This code doesn't seem to work. I don't think it's traversing the tree yet. When it reaches the nested call to recursion it passes in the whole array not just the section which needs to be traversed.

Also, the c_id is just a customer id and is not needed. The parent_id and p_id (project id) should be the only ids needed.

I tried this so far:

Code:

$Project->Children = $this->Recurs($Project, $Project->parent_id);

But I get an empty array. My understanding is that I should be passing the current project to check for child elements and also the current parent id but I just feel like there's a lot of code missing.

EDITED

I did it, but I nearly gave up computing altogether in the middle:

Code:

function Recurs($Projects, $ParentID = NULL)
  {
   $Ret = array();
   foreach($Projects as $Project)
   {
    if($Project->parent_id != $ParentID) // skip those rows not related to the parent
     continue;
    
    $Project->Children = $this->Recurs($Projects, $Project->p_id); // find any child rows related to this project
    $Ret[$Project->p_id] = $Project; // add the project to the
   }
   return $Ret;
  }

Thanks gRoberts.