PHP + MySQL: Finding total of values by recursing through mySQL parent children groups

Question

I have a mySQL table like this:

Folders

[id] [name] [parent_id]
1    fruits  0
2    orange  1
3    lemon   2
4    steak   0

Projects

[id] [name]     [parent_id]   [hours]
1    project1    1            3
2    project2    2            4
3    project3    3            6
4    project4    4            7

Basically projects can sit within different folders. the parent_id of projects is the id of folder. Folders can have subfolders and projects can reside within sub folders. And there is unlimited # of subfolders.

The result I would like to get is find total hours within a folder and all it's sub folders. SO basically going trough folder and finding all children folders within that folder (and keep doing it til it reach deepest level) and adding all the hours for all projects within each of those sub folders to get a total hours.

Think of it as a task or projects database and I want to calculate total hours spent on project.

Is there a way to do this via mySQL or PHP?

So far, I have the following info for mySQL. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

And I have this PHP function but problem is I dont know how to add the total from the results. I dont know how to append an array on a recursive function. It's probably wrong anyways but I thought I'll just throw it out there.

function categoryChild($id) {
    $s = "SELECT id FROM folders WHERE parent_id = $id";
    $r = mysql_query($s);
    $children = array();
    $hours = array();
    if(mysql_num_rows($r) > 0) {
        # It has children, let's get them.
        while($row = mysql_fetch_array($r)) {
            # Add the child to the list of children, and get its subchildren
            $ee['id'] = categoryChild($row['id']);
            $newid = $row['id'];
            $sql = "SELECT SUM(b.hours) as totalhours
            FROM folders a
            INNER JOIN projects b ON b.folder_id = a.id
            WHERE a.id = '$newid'";
            $result = mysql_query($sql);
            $children['hours'] = $row['totalhours'];

            }
        } 
        $s1 = "SELECT sum(hours) as totalhours FROM projects WHERE folder_id = $id";
        $r1 = mysql_query($s1);
        if(mysql_num_rows($r1) > 0) {
            while($row3 = mysql_fetch_array($r1)) {
            $children['hours'] = $row3['totalhours'];
            }
        }

    $data = $data['hours'];
    return $data;
}

Have you investigated using nested sets as described in the mysql link you have there? The nested sets model would be a good fit if you have a read heavy application but loses it's effectiveness if you have to do a lot of writes compared to reads. — rojoca, Nov 01 '10 at 18:15
I'd rather use a closure table. See [Bill Karwin's slides](http://www.slideshare.net/billkarwin/models-for-hierarchical-data), it includes an example of deep tree searches for the adjacency model, and possible alternatives for your current setup. — Wrikken, Nov 01 '10 at 18:37

score 1 · Answer 1 · answered Nov 03 '10 at 04:48

It was tough... but I think I finally solved this.

$totalhours = getSubfolders($id);
$totalhours = findTotalHours($totalhours,$id);


function findTotalHours($array,$id) {
 global $db, $user_id;
 $array = array_keys_multi($array);
 $array[] = $id; // i wanted to get all projects under starting folder
 $totalhours = implode(',',$array);
 $sql = "SELECT SUM(hours) AS totalhours FROM projects WHERE folder_id IN ($totalhours)";
 $row = $db->query_first($sql); 
 return $row['totalhours'];
}

function getSubfolders ($id) { //http://stackoverflow.com/questions/2398402/recursive-function-to-get-all-the-child-categories
    $s = "SELECT id FROM folders WHERE parent_id = $id";
    $r = mysql_query($s);
    $children = array();
    if(mysql_num_rows($r) > 0) {
        # It has children, let's get them.
        while($row = mysql_fetch_array($r)) {
            # Add the child to the list of children, and get its subchildren
            $children[$row['id']] = getSubfolders($row['id']);
        }
    }
    return $children;
}

function array_keys_multi(array $array) //http://codeaid.net/php/extract-all-keys-from-a-multidimensional-array
{
 $keys = array();
 foreach ($array as $key => $value) {
  $keys[] = $key;
  if (is_array($array[$key])) {
   $keys = array_merge($keys, array_keys_multi($array[$key]));
  }
 }
 return $keys;
}

score 0 · Answer 2 · answered Nov 01 '10 at 20:56

I'm not sure I follow what you are trying to do with your code, but here is a skeleton of how I would organize it.

function getTotalHours( $id) {

  $sum = getHoursOnProject( $id);
  foreach( getChildIds( $id) as $childId) {
    $sum += getTotalHours( $childId);
  }
  return $sum;
}

function getChildIds( $id) {
  $query = "SELECT id FROM folders WHERE parent_id = $id";
  ...
  return $childIdArray;
}

function getHoursOnProject( $id) {
  $query = "SELECT sum(hours) as totalhours FROM projects WHERE folder_id = $id";
  ...
  return $hours;
}

PHP + MySQL: Finding total of values by recursing through mySQL parent children groups

2 Answers2