PHP + MySQL：通过递归遍历mySQL父子组来查找总值

Question

我有一个像这样的 mySQL 表：

文件夹

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

项目

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

基本上项目可以位于不同的文件夹中。项目的parent_id是文件夹的id。文件夹可以有子文件夹，项目可以驻留在子文件夹中。并且子文件夹的数量没有限制。

我想要得到的结果是找到文件夹及其所有子文件夹中的总小时数。所以基本上是遍历文件夹并找到该文件夹​​中的所有子文件夹（并继续执行直到达到最深级别），然后添加每个子文件夹中所有项目的所有小时数以获得总小时数。

将其视为任务或项目数据库，我想计算项目上花费的总时间。

有没有办法通过 mySQL 或 PHP 来做到这一点？

到目前为止，我有 mySQL 的以下信息。 http://mikehillyer.com/articles/managing-hierarchical-data-in- mysql/

我有这个 PHP 函数，但问题是我不知道如何从结果中添加总数。我不知道如何在递归函数上附加数组。无论如何，这可能是错误的，但我想我会把它扔掉。

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;
}

Answer 1

这很困难......但我想我终于解决了这个问题。

$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;
}

Answer 2

我不确定我是否遵循您尝试对代码执行的操作，但这里是我如何组织它的框架。

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;
}