二、模型
这里我用一个简单食品目录作为我们的示例数据。
我们的数据结构是这样的,以下是代码:
代码如下:
Food
|---Fruit
| |---Red
| | |--Cherry
| +---Yellow
| +--Banana
+---Meat
|--Beef
+--Pork
代码如下:
Food : 食物
Fruit : 水果
Red : 红色
Cherry: 樱桃
Yellow: 黄色
Banana: 香蕉
Meat : 肉类
Beef : 牛肉
Pork : 猪肉
代码如下:
+-----------------------+
| parent | name |
+-----------------------+
| | Food |
| Food | Fruit |
| Fruit | Green |
| Green | Pear |
| Fruit | Red |
| Red | Cherry |
| Fruit | Yellow |
| Yellow | Banana |
| Food | Meat |
| Meat | Beef |
| Meat | Pork |
+-----------------------+
代码如下:
// $parent is the parent of the children we want to see
// $level is increased when we go deeper into the tree,
// used to display a nice indented tree
function display_children($parent, $level) {
// 获得一个 父节点 $parent 的所有子节点
$result = mysql_query("
SELECT name
FROM tree
WHERE parent = '" . $parent . "'
;"
);
// 显示每个子节点
while ($row = mysql_fetch_array($result)) {
// 缩进显示节点名称
echo str_repeat(' ', $level) . $row['name'] . "\n";
//再次调用这个函数显示子节点的子节点
display_children($row['name'], $level+1);
}
}
?>
代码如下:
Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork
代码如下:
// $node 是那个最深的节点
function get_path($node) {
// 查询这个节点的父节点
$result = mysql_query("
SELECT parent
FROM tree
WHERE name = '" . $node ."'
;"
);
$row = mysql_fetch_array($result);
// 用一个数组保存路径
$path = array();
// 如果不是根节点则继续向上查询
// (根节点没有父节点)
if ($row['parent'] != '') {
// the last part of the path to $node, is the name
// of the parent of $node
$path[] = $row['parent'];
// we should add the path to the parent of this node
// to the path
$path = array_merge(get_path($row['parent']), $path);
}
// return the path
return $path;
}
?>;
代码如下:
Array (
[0] => Food
[1] => Fruit
[2] => Red
)
代码如下:
1 Food 18
+------------------------------+
2 Fruit 11 12 Meat 17
+-------------+ +------------+
3 Red 6 7 Yellow 10 13 Beef 14 15 Pork 16
4 Cherry 5 8 Banana 9
代码如下:
+----------+------------+-----+-----+
| parent | name | lft | rgt |
+----------+------------+-----+-----+
| | Food | 1 | 18 |
| Food | Fruit | 2 | 11 |
| Fruit | Red | 3 | 6 |
| Red | Cherry | 4 | 5 |
| Fruit | Yellow | 7 | 10 |
| Yellow | Banana | 8 | 9 |
| Food | Meat | 12 | 17 |
| Meat | Beef | 13 | 14 |
| Meat | Pork | 15 | 16 |
+----------+------------+-----+-----+
代码如下:
+------------+-----+-----+
| name | lft | rgt |
+------------+-----+-----+
| Food | 1 | 18 |
| Fruit | 2 | 11 |
| Red | 3 | 6 |
| Cherry | 4 | 5 |
| Yellow | 7 | 10 |
| Banana | 8 | 9 |
| Meat | 12 | 17 |
| Beef | 13 | 14 |
| Pork | 15 | 16 |
+------------+-----+-----+
代码如下:
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;
代码如下:
+------------+-----+-----+
| name | lft | rgt |
+------------+-----+-----+
| Fruit | 2 | 11 |
| Red | 3 | 6 |
| Cherry | 4 | 5 |
| Yellow | 7 | 10 |
| Banana | 8 | 9 |
+------------+-----+-----+
代码如下:
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11 ORDER BY lft ASC;
代码如下:
function display_tree($root) {
// 得到根节点的左右值
$result = mysql_query("
SELECT lft, rgt
FROM tree
WHERE name = '" . $root . "'
;"
);
$row = mysql_fetch_array($result);
// 准备一个空的右值堆栈
$right = array();
// 获得根基点的所有子孙节点
$result = mysql_query("
SELECT name, lft, rgt
FROM tree
WHERE lft BETWEEN '" . $row['lft'] . "' AND '" . $row['rgt'] ."'
ORDER BY lft ASC
;"
);
// 显示每一行
while ($row = mysql_fetch_array($result)) {
// only check stack if there is one
if (count($right) > 0) {
// 检查我们是否应该将节点移出堆栈
while ($right[count($right) - 1] <$row['rgt']) {
array_pop($right);
}
}
// 缩进显示节点的名称
echo str_repeat(' ',count($right)) . $row['name'] . "\n";
// 将这个节点加入到堆栈中
$right[] = $row['rgt'];
}
}
?>
代码如下:
SELECT name FROM tree WHERE lft <4 AND rgt >; 5 ORDER BY lft ASC;
代码如下:
+------------+
| name |
+------------+
| Food |
| Fruit |
| Red |
+------------+
代码如下:
descendants = (right – left - 1) / 2
代码如下:
function rebuild_tree($parent, $left) {
// the right value of this node is the left value + 1
$right = $left+1;
// get all children of this node
$result = mysql_query("
SELECT name
FROM tree
WHERE parent = '" . $parent . "'
;"
);
while ($row = mysql_fetch_array($result)) {
// recursive execution of this function for each
// child of this node
// $right is the current right value, which is
// incremented by the rebuild_tree function
$right = rebuild_tree($row['name'], $right);
}
// we've got the left value, and now that we've processed
// the children of this node we also know the right value
mysql_query("
UPDATE tree
SET
lft = '" . $left . "',
rgt= '" . $right . "'
WHERE name = '" . $parent . "'
;"
);
// return the right value of this node + 1
return $right + 1;
}
?>
代码如下:
rebuild_tree('Food',1);
代码如下:
UPDATE tree SET rgt = rgt + 2 WHERE rgt > 5;
UPDATE tree SET lft = lft + 2 WHERE lft > 5;
代码如下:
INSERT INTO tree SET lft=6, rgt=7, name='Strawberry';
四、结语
好了,现在你可以用两种不同的方法设计你的多级数据库结构了,采用何种方式完全取决于你个人的判断,但是对于层次多数量大的结构我更喜欢第二种方法。如果查询量较小但是需要频繁添加和更新的数据,则第一种方法更为简便。
另外,如果数据库支持的话 你还可以将rebuild_tree()和 腾出空间的操作写成数据库端的触发器函数, 在插入和更新的时候自动执行, 这样可以得到更好的运行效率, 而且你添加新节点的SQL语句会变得更加简单。