我有一个查询(+php)来获取父级的所有子项,但现在将其翻转过来以便我得到一个元素的所有父级会更有用。
桌子:
ID parentID showOrder
===============================
1 0 0
2 1 0
3 2 0
4 3 1
5 3 2
6 3 3
法典:
<?php
$structure = array();
$sql = "SELECT ID, parentID FROM table ORDER BY parentID ASC, showOrder ASC";
while ($row = fetch_row()) {
$structure[$row['parentID']][] = $row['ID'];
}
输出:
0: [1], 1: [2], 2: [3], 3: [5,4,6]
首选结果:
0: [], 1: [0], 2: [1, 0], 3: [2, 1, 0],
4: [3, 2, 1, 0], 5: [3, 2, 1, 0], 6: [3, 2, 1, 0]
我该怎么做?
您属于分层查询域。
尽管 Oracle 提供了一种非常方便的处理方式(CONNECT BY
条款),但 mysql 没有。
点击此链接 http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/将向您解释如何通过纯mysql实现目标。
如果你希望只通过 php 来做到这一点,你也可以,但它在某种程度上会效率低下,因为引擎切换(php/mysql 之间的通信:调用查询、回到 php 等等)可能会在多次迭代后变得昂贵。
RGDS。
如果父项总是井井有条,你可以像这样保留祖先的数组:
// initialize as empty
$ancestors = array();
while ($row = fetch_row()) {
$ancestors[$row['id']] = array($row['parentid'] => $row['parentid']);
// if the parent exists
if (isset($ancestors[$row['parentid']])) {
// grow ancestors by parent's ancestors
$ancestors[$row['id']] += $ancestors[$row['parentid']];
}
}
输出:
Array
(
[1] => Array
(
[0] => 0
)
[2] => Array
(
[1] => 1
[0] => 0
)
[3] => Array
(
[2] => 2
[1] => 1
[0] => 0
)
[4] => Array
(
[3] => 3
[2] => 2
[1] => 1
[0] => 0
)
[5] => Array
(
[3] => 3
[2] => 2
[1] => 1
[0] => 0
)
[6] => Array
(
[3] => 3
[2] => 2
[1] => 1
[0] => 0
)
)