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

Question

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.

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 "
<ul>
";
    }
    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>

";
    }
    if ($level > 0 && $stmt->num_rows > 0) {
        echo "</ul>
";
    }
    $stmt->close();
}

Answer 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:

$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.

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.

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);