选择terms (categories) van mysql表并在选择框中输出它们

Question

这是我的terms表(类别)的数据库结构：

   id   |   name   |   parent_id   |   level   |   position   
--------------------------------------------------------------
   1    |   term 1 |   NULL        |   0       |   1
   2    |   term 2 |   1           |   1       |   1
   3    |   term 3 |   1           |   1       |   2
   4    |   term 4 |   NULL        |   0       |   2
   5    |   term 5 |   4           |   1       |   1

因此，术语2和3是1的第一级子项，5是4的第一级子项

这是我的问题：(这是不正确的，这应该被修复)

SELECT
    `id`,
    `name`
FROM
    `terms`
ORDER BY
    `position` ASC,
    `level` ASC

这是我的php：

$terms = array();

// query part

if(!$this->_db->resultRows > 0)
  return $terms;

while($d = $this->_db->fetch($r))
{
  $terms[$d->id] = new Term($d->id);
}     

return $terms;

当前结果：

term 1
  term 2
  term 5
term 4
 term 3

但结果应该是：

term 1
  term 2
  term 3
term 4
  term 5

我不知道如何更改查询以获得正确的结果

目标是在(多个)选择框中输出

我知道如何使用嵌套列表，但您不能嵌套select

Answer 1

好的，有很多方法可以处理这种类型的问题。我将介绍的所有方法都不需要在表中有level列。在我看来，这是冗余数据，因为它可以从其他列中找到的信息中推断出来。

如果您知道最大“级别”将仅为1(最大深度为2)，则可以使用如下查询：

SELECT
        t.`id`,
        t.`name`,
        IF(p.`position` IS NULL, t.`position`*{$row_count}, p.`position`*{$row_count}+t.`position`) AS display_order
FROM
        `terms` t
        LEFT JOIN `terms` p ON p.`id` = t.`parent_id`
ORDER BY
        display_order

其中$row_count的计算方式为：

SELECT COUNT(*) FROM `terms`

有一些方法可以修改这个SQL，使其与更多的级别(深度)一起工作，但查询需要随着它支持的每个最大级别/深度而变得更大。

如果您不确定您将拥有的级别数量，那么您可能应该这样做：

$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$sth = $dbh->prepare('SELECT id,name FROM terms WHERE parent_id IS NULL ORDER BY position');
$sth->execute(array(NULL));
$terms = $sth->fetchAll();

$sql = 'SELECT id,name FROM terms WHERE parent_id = ? ORDER BY position';
$terms_to_check = $terms;
$terms = array();
while(count($terms_to_check))
{
        $k = array_shift($terms_to_check);
        $terms[] = $k;
        $sth = $dbh->prepare($sql);
        $sth->execute(array($k['id']));
        $results = $sth->fetchAll();
        $terms_to_check = array_merge($results, $terms_to_check);
}

(顺便说一句，我推荐使用PDO。)