MySQL计算所有子节点，不管有多少

Question

我正在开发一个简单的系统，它有无限的类别和每个类别中的项目。例如，类别中可能有类别等(类别1 ->类别2 ->类别3)，所有类别都包含项目。我想显示每个类别及其所有子类别的总项目。我试图找出一个循环来做这个，但基本上是短的。我在PHP/MySQL建设。我的类别表模式是类别(id, id_parent) id是自动递增的，id_parent是它是否驻留在另一个类别中(0是默认的)。我的项目模式是items(id, id_category)。显然还有其他的专栏，但我相信这些是唯一重要的。有人能帮我开发一个循环结构，提出了他们所处的类别的项目总量(计算所有子类别的项目)。这是我的开头，尽管我觉得它非常非常错误。

function CountChildDownloads($id_category)
{
global $smcFunc;
$x = array();
$total = 0;
$request = $smcFunc['db_query']('', '
    SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
    WHERE id_category = '.$id_category.''
);
$total += $request['items'];
$request = $smcFunc['db_query']('', '
    SELECT id FROM {db_prefix}xld_categories
    WHERE id_parent = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0)  {
    while($row = $smcFunc['db_fetch_assoc']($request)) {
        $x[] = $row['id'];
    }
}
foreach ($x as $id)
{
    $y = array();
    $z = 0;
    $request = $smcFunc['db_query']('', '
        SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
        WHERE id_category = '.$id.''
    );
    $z += $request['items'];
    $request = $smcFunc['db_query']('', '
        SELECT id FROM {db_prefix}xld_categories
        WHERE id_parent = '.$id.''
    );
    if($smcFunc['db_num_rows']($request) > 0)  {
        while($row = $smcFunc['db_fetch_assoc']($request)) {
            $y[] = $row['id'];
        }
    }
    while (count($y) > 0)
    {
        $id_y = $y[0];
        $request = $smcFunc['db_query']('', '
            SELECT id FROM {db_prefix}xld_categories
            WHERE id_parent = '.$id_y.''
        );
        if($smcFunc['db_num_rows']($request) > 0)  {
            while($row = $smcFunc['db_fetch_assoc']($request)) {
                $y[] = $row['id'];
            }
        }
        $request = $smcFunc['db_query']('', '
            SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
            WHERE id_category = '.$id_y.''
        );
        $z += $request['items'];
        unset($y[0]);
        array_values($y);
    }
    $total += $z;
}
return $total;
}

$smcFunc只是系统执行查询的方式。它是一个MySQL后端。

工作函数(将需要更新到标准的MySQL调用，如果不使用SMF)谢谢卢卡斯。

function CountChildDownloads($id_category)
{
global $smcFunc;
$x = array();
$total = array();
$total['downloads'] = 0;
$total['views'] = 0;
$request = $smcFunc['db_query']('', '
    SELECT views FROM {db_prefix}xld_downloads
    WHERE id_category = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0)  {
    while($row = $smcFunc['db_fetch_assoc']($request)) {
        $total['downloads']++;
        $total['views'] += $row['views'];
    }
}
$request = $smcFunc['db_query']('', '
    SELECT id FROM {db_prefix}xld_categories
    WHERE id_parent = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0)  {
    while($row = $smcFunc['db_fetch_assoc']($request)) {
        $x[] = $row['id'];
    }
}
foreach ($x as $id)
{
    $y = array();
    $z = 0;
    $w = 0;
    $request = $smcFunc['db_query']('', '
        SELECT views FROM {db_prefix}xld_downloads
        WHERE id_category = '.$id.''
    );
    if($smcFunc['db_num_rows']($request) > 0)  {
        while($row = $smcFunc['db_fetch_assoc']($request)) {
            $z++;
            $w += $row['views'];
        }
    }
    $request = $smcFunc['db_query']('', '
        SELECT id FROM {db_prefix}xld_categories
        WHERE id_parent = '.$id.''
    );
    if($smcFunc['db_num_rows']($request) > 0)  {
        while($row = $smcFunc['db_fetch_assoc']($request)) {
            $y[] = $row['id'];
        }
    }
    while (count($y) > 0)
    {
        $id_y = $y[0];
        if (!empty($id_y))
        {
            $request = $smcFunc['db_query']('', '
                SELECT id FROM {db_prefix}xld_categories
                WHERE id_parent = {int:id_parent}',
                array(
                    'id_parent' => $id_y,
                )
            );
            if($smcFunc['db_num_rows']($request) > 0)  {
                while($row = $smcFunc['db_fetch_assoc']($request)) {
                    $y[] = $row['id'];
                }
            }
            $request = $smcFunc['db_query']('', '
                SELECT views FROM {db_prefix}xld_downloads
                WHERE id_category = {int:id_category}',
                array(
                    'id_category' => $id_y,
                )
            );
            if($smcFunc['db_num_rows']($request) > 0)  {
                while($row = $smcFunc['db_fetch_assoc']($request)) {
                    $z++;
                    $w += $row['views'];
                }
            }
        }
        unset($y[0]);
        $y = array_values($y);
    }
    $total['downloads'] += $z;
    $total['views'] += $w;
}
return $total;
}

Answer 1

嵌套循环/查询有很多方法。结构更改的想法是使用一个单独的表，列出每个类别的所有子类别。确保它不仅有直接子元素，还有子元素和子元素……比如1有子结点2 2有子结点3 1有子结点3 3有子结点5 1有子结点5。等. .)但是，就目前的情况来看……

一个循环结构可以是:

开始记录集。||查询所有parent = 0的类别id。||将每个添加到数组(X)中关闭结果集。

对于数组(X)中的每个id:

• 建立一个新的计数变量(z)。

• 建立一个新的子id数组(Y)。

• 开始记录集。||查询所有类别=当前id x ||项的计数*添加到计数变量(z) ||关闭结果集。

• 开始记录集。||查询parent =当前id x ||的所有类别id将所有添加到子id数组(Y)关闭结果集。

• while子数组(Y)长度> 0

  • 类别id y =数组(y)中的第一项

  • 开始记录集。||查询parent =当前id y的所有类别id。||将所有添加到子id数组(Y)关闭结果集。

  • 开始记录集。||查询类别=当前id y ||的所有项目的计数*添加到计数变量(z) ||关闭结果集。

  • 从数组(Y)中移除第一项

• continue while loop

• 在这一点上，你有最终的项目计数(z)类别id x…对它做点什么，然后继续执行for循环

结束for循环