我一直在努力解决这个问题,但没有取得任何进展。希望有人能来救我。
我的问题是,我使用邻接列表数据模型在mysql中生成层次结构数据。我可以将表格(见下文)检索到一个多维数组中,每个项目都有关联数组。我想做的是,一旦我得到这个数组,我就想得到另一个数组,其中包含父id(包括父项)下的所有节点(子、孙子等)。我只是无法训练如何在php中编写tihs代码。
在MySQL中,我的表如下所示:
id name parent_id
1 Electronics 0
2 Televisions 1
3 Portable Electronics 1
4 Tube 2
5 LCD 2
6 Plasma 2
7 Mp3 Players 3
8 CD Players 3
9 2 Way Radios 3
10 Flash 7
我可以用这个代码将所有行检索到一个关联数组中。
$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
$data[] = $row;
}
获取结果:
Array
(
[0] => Array
(
[id] => 1
[name] => Electronics
[parent_id] => 0
)
[1] => Array
(
[id] => 2
[name] => Televisions
[parent_id] => 1
)
[2] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
)
[3] => Array
(
[id] => 4
[name] => Tube
[parent_id] => 2
)
[4] => Array
(
[id] => 5
[name] => LCD
[parent_id] => 2
)
[5] => Array
(
[id] => 6
[name] => Plasma
[parent_id] => 2
)
[6] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
)
[7] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
)
[8] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
)
[9] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
)
)
有了这些结果,我想用id过滤掉。
举个例子,我想要一个id为3的便携式电子设备下每个节点的关联数组。(使用id表示代码)
它将返回一个带有id的行的数组:
- 3便携式电子产品(必须包括选定的家长)
- 7 Mp3播放器(儿童)
- 8个CD播放器(儿童)
- 9个双向收音机(儿童)
- 10 Flash(Grand Child)
如果Flash有孩子,它也会归还这些孩子。
因此,最终结果将返回一个类似于上面的数组,但只包含这些项。
请注意:我不想要一个创建树结构多维数组的函数(已经有了解决方案)。我想构建一个函数:fetch_recurive($id),它接收一个id并返回该级别和以下级别中的所有项目等。
希望这能帮助
提前感谢
编辑:
我之前发布了一个解决方案,可以根据您提供的输出构建多维数组,还可以从该特定数组中获取特定id
的所有子元素。我现在已经知道了如何直接从输出中检索子元素(而不必首先通过buildtree()
函数:
function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array())
{
foreach($src_arr as $row)
{
if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid)
{
$rowdata = array();
foreach($row as $k => $v)
$rowdata[$k] = $v;
$cats[] = $rowdata;
if($row['parent_id'] == $currentid)
$cats = array_merge($cats, fetch_recursive($src_arr, $row['id'], true));
}
}
return $cats;
}
要使用上面的函数,只需将输出数组$data
传递给第一个参数,并将要从中检索子元素的id
传递给第二个参数:
例如:
$list = fetch_recursive($data, 3);
它应该为id
3
提供正确的数组结构(如本答案最后一个代码框中的示例所示)。
原始答案:
直到现在,我还从来没有抽出时间编写递归函数来用这种设计构建嵌套树。我相信还有很多其他人写过类似的函数,但这一个肯定对你有用:
function buildtree($src_arr, $parent_id = 0, $tree = array())
{
foreach($src_arr as $idx => $row)
{
if($row['parent_id'] == $parent_id)
{
foreach($row as $k => $v)
$tree[$row['id']][$k] = $v;
unset($src_arr[$idx]);
$tree[$row['id']]['children'] = buildtree($src_arr, $row['id']);
}
}
ksort($tree);
return $tree;
}
这个函数将递归地从邻接列表中构建一个树,并保持id的升序。这也使得每个父/子的id
成为每个信息数组的密钥。
此代码:
$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
$data[] = $row;
}
echo '<pre>';
print_r(buildtree($data));
echo '</pre>';
会输出这样的东西:
Array
(
[1] => Array
(
[id] => 1
[name] => Electronics
[parent_id] => 0
[children] => Array
(
[2] => Array
(
[id] => 2
[name] => Televisions
[parent_id] => 1
[children] => Array
(
[4] => Array
(
[id] => 4
[name] => Tube
[parent_id] => 2
[children] => Array()
)
[5] => Array
(
[id] => 5
[name] => LCD
[parent_id] => 2
[children] => Array()
)
[6] => Array
(
[id] => 6
[name] => Plasma
[parent_id] => 2
[children] => Array()
)
)
)
[3] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
[children] => Array
(
[7] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
[children] => Array
(
[10] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
[children] => Array()
)
)
)
[8] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
[children] => Array()
)
[9] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
[children] => Array()
)
)
)
)
)
)
要将特定id
的所有子节点放到一维数组中,可以使用以下函数:
function fetch_recursive($tree, $parent_id, $parentfound = false, $list = array())
{
foreach($tree as $k => $v)
{
if($parentfound || $k == $parent_id)
{
$rowdata = array();
foreach($v as $field => $value)
if($field != 'children')
$rowdata[$field] = $value;
$list[] = $rowdata;
if($v['children'])
$list = array_merge($list, fetch_recursive($v['children'], $parent_id, true));
}
elseif($v['children'])
$list = array_merge($list, fetch_recursive($v['children'], $parent_id));
}
return $list;
}
基于上面的buildtree()
函数,假设我们想要获得id
3:的所有子节点
echo '<pre>';
print_r(fetch_recursive(buildtree($a), 3));
echo '</pre>';
这将输出:
Array
(
[0] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
)
[1] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
)
[2] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
)
[3] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
)
[4] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
)
)
这里有一种方法可以让您更进一步,您可以决定如何构建结果数组以及选择包含哪些字段。这还没有经过测试,但您应该看到其中的逻辑。
// connect to db
// set id counter
$ids = 0;
// declare array
$categories = new Array();
// determine max ids
$query = mysql_query("SELECT COUNT(1) AS ids FROM test");
$result = mysql_fetch_array(query); // get result
$count = $result['ids'];
// loop through ids for parents
for($ids = 0; $ids <= $count; $ids++) {
$query1 = mysql_query("SELECT * FROM test WHERE id = '" . $ids . "'");
$query2 = mysql_query("SELECT id, name, parent_id FROM test WHERE parent_id = '" . $ids . "'");
// check if has children
if(mysql_num_rows($query2) > 0) {
// iterate through children and add to array
while (mysql_fetch_array($query2) as $row) {
$categories[$ids]['child'][$row['id']] = $row['name'];
}
}
// check if has siblings
if(mysql_num_rows($query1) > 0) {
// iterate through children and add to array
while (mysql_fetch_array($query2) as $row) {
$categories[$ids]['sibling'][$row['id']] = $row['name'];
}
}
}