在PHP中使用数组而不是大量的数据库查询

My function looks like that. It works but does lots of work (recursively calls itself and does lots of db queries.). There must be another way to do same thing but with array (with one query). I can't figure out how to modify this function to get it work with array. Please help.

我的功能看起来像那样。它工作但很多工作(递归调用自身并执行大量的数据库查询。)。必须有另一种方法来做同样的事情,但使用数组(有一个查询)。我无法弄清楚如何修改此函数以使其与数组一起使用。请帮忙。

function genMenu($parent, $level, $menu, $utype) {
    global $db;
    $stmt=$db->prepare("select id, name FROM navigation WHERE parent = ? AND menu=? AND user_type=?") or die($db->error);
    $stmt->bind_param("iii", $parent, $menu, $utype) or die($stmt->error);
    $stmt->execute() or die($stmt->error);

    $stmt->store_result();
    /* bind variables to prepared statement */
$stmt->bind_result($id, $name) or die($stmt->error);
    if ($level > 0 && $stmt->num_rows > 0) {
        echo "\n<ul>\n";
    }
    while ($stmt->fetch()) {
        echo "<li>";
        echo '<a href="?page=' . $id . '">' . $name . '</a>';
        //display this level's children
        genMenu($id, $level+1, $menu, $utype);
        echo "</li>\n\n";
    }
    if ($level > 0 && $stmt->num_rows > 0) {
        echo "</ul>\n";
    }
    $stmt->close();
}

4 个解决方案

#1

You can build a tree-based array fairly easily, so it'd be one single query and then a bunch of PHP logic to do the array building:

你可以很容易地构建一个基于树的数组,所以它是一个单一的查询,然后是一堆PHP逻辑来进行数组构建:

$tree = array();
$sql = "SELECT id, parent, name FROM menu WHERE parent ... etc.... ";
$results = mysql_query($sql) or die(mysql_error());
while(list($id, $parent, $name) = mysql_fetch_assoc($results)) {
    $tree[$id] = array('name' => $name, 'children' => array(), 'parent' => $parent);
    if (!array_key_exists($tree[$parent]['children'][$id])) {
        $tree[$parent]['children'][$id] = $id;
    }
}

For this, I'm assuming that your tree has a top-level '0' node. if not, then you'll have to adjust things a bit.

为此,我假设您的树有一个*'0'节点。如果没有,那么你将不得不调整一下。

This'd give you a double-linked tree structure. Each node in the tree has a list of its children in the ['children'] sub-array, and each node in the tree also points to its parent via the ['parent'] attribute.

这将为您提供双链接树结构。树中的每个节点在['children']子数组中都有一个子节点列表,树中的每个节点也通过['parent']属性指向其父节点。

Given a certain starting node, you can traverse back up the tree like this:

给定一个起始节点,您可以像这样遍历树:

$cur_node = 57; // random number
$path = array();
do {
    $parent = $tree[$cur_node]['parent'];
    $path[] = $parent;
    $cur_node = $parent;
} while ($parent != 0);

#2

I think the first thing you can fix is removing the WHERE parent = ? clause and then work on the resulting query result, this will make you work a bit more in managing the result but will definitely safe you IO operations.

我认为你可以解决的第一件事就是删除WHERE parent =?子句然后处理生成的查询结果,这将使您在管理结果方面工作更多,但肯定会保护您的IO操作。

Using parts of Marc B Solution

使用Marc B Solution的部分内容

$tree = array();
$sql = "select id, parent, name FROM navigation AND menu=? AND user_type=?";
$results = mysql_query($sql) or die(mysql_error());
while(list($id, $parent, $name) = mysql_fetch_assoc($results)) {
    $tree[$id] = array('name' => $name, 'children' => array(), 'parent' => $parent);
    if (!array_key_exists($tree[$parent]['children'][$id])) {
        $tree[$parent]['children'][$id] = $id;
    }
}

print_r($tree);

Replace the ? with the actual values, and give that a run, what is your output?

更换 ?用实际值,并给出一个运行,你的输出是什么?

#3

Maybe not what you wanted, but it is great when it comes to trees. You would have to rebuild your table and had some code to output the html, but you would have only one query. It could be worth the effort on the long run.

也许不是你想要的,但它对树木很有用。你必须重建你的表并有一些代码输出html,但你只有一个查询。从长远来看,这可能是值得的。

ie.If you have this menu

即如果你有这个菜单

# Menu hierarchy:
 - Home
 - Product
    |- Tv
    |- Radio
 - About us

It would looks like this in the db.

它在db中看起来像这样。

+----+----------+-----------+-----+-----+
| id | menu     | parent_id | lft | rgt |
+----+----------+-----------+-----+-----+
| 1  | Home     | null      | 1   | 2   |
+----+----------+-----------+-----+-----+
| 2  | Product  | null      | 3   | 8   |
+----+----------+-----------+-----+-----+
| 3  | Tv       | 2         | 4   | 5   |
+----+----------+-----------+-----+-----+
| 4  | Radio    | 2         | 6   | 7   |
+----+----------+-----------+-----+-----+
| 5  | About us | null      | 9   | 10  |
+----+----------+-----------+-----+-----+

The data could be fetch using a similar query

可以使用类似的查询来获取数据

$select = "SELECT * FROM table_name WHERE lft BETWEEN 3 AND 8;"

To output a specific menu:

要输出特定菜单:

 - Product
    |- Tv
    |- Radio

I know its not exactly the answer your were looking for, but FYI, there are other ways to use hierarchical tree data.

我知道它不是你正在寻找的答案,但仅供参考,还有其他方法可以使用分层树数据。

Good luck.

#4

I wrote in the past an ugly way but with simple SELECT:

我过去用一种丑陋的方式写了但是用简单的SELECT:

I store in text/varchar field strings like this:

我存储在text / varchar字段字符串中,如下所示:

/001
/001/001
/001/002
/002
/002/001
/002/001/001

Ignore the hebrew and look in window.aMessages array, to look how it works: http://www.inn.co.il/Forum/Forum.aspx/t394009#4715854

忽略希伯来语并查看window.aMessages数组,看看它是如何工作的:http://www.inn.co.il/Forum/Forum.aspx/t394009#4715854

#1