如何在PHP中循环进入此查询？

Question

I'm making a new site and I'm struggling with the menu. Some elements (parent: 0) don't have children and some others do have 1 or 2. I'm trying a new approach to make things shorter, safer and easier but I'm stuck at something. Here's my code:

My problem is that inside the FOR LOOP I do get the amount of sub-menu otems correctly but obviously the 'label' is the same as the parent as that is the ARRAY key already in the loop. The original author of this idea at http://wizardinternetsolutions.com/articles/web-programming/dynamic-multilevel-css-menu-php-mysql fixed it with a $label+1 but I'm not executing the SQL search all over again. What should I do?

UPDATE: Here's the complete code that involves that function:

public function sQuery() {
$sql = "SELECT a.id, a.label, a.link, Deriv1.Count
    FROM `menu` a
    LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1
    ON a.id = Deriv1.parent
    WHERE a.parent = :parent";

try {
    $core = Core::getInstance();
    $stmt = $core->dbh->prepare($sql);
    $stmt->bindParam(':parent', $this->_mParent, PDO::PARAM_INT);

    if ($stmt->execute()) {
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        while ($menuItem = $stmt->fetch()) {
            echo "<li class=\"dropdown\">
";
            echo "<a href=\"".$menuItem['link']."\" class=\"dropdown-toggle js-activated\">".$menuItem['label']."</a>
";

            if ($menuItem['Count'] > 0) {                           
                echo "<ul class=\"dropdown-menu\">
";}

                // THIS IS THE PART THAT DOESN'T GET ME THE CORRECT INFO
                for ($i = 1; $i <= $menuItem['Count']; $i++) {
                    echo "<li><a href=\"".$menuItem['link']."\">".$menuItem['label']."</a></li>
";
                }

                echo "</ul>
";
            }

            echo "</li><!-- /.dropdown -->
";
        }
    }
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

}

The result should be:

• Home
• Browse Catalog
• ------ Browse per category
• ------ Browse per ID
• About Us

and I'm getting:

• Home
• Browse Catalog
• ------ Browse Catalog
• ------ Browse Catalog
• About Us

Answer 1

NVM I found the solution. I added a new column to the menu table called position (0 if it's a main menu, 1 if it's a child, etc) and built a full array from the query and then split that into 3 new arrays depending on each row's position:

<?
$sql = "SELECT a.id, a.label, a.link, a.parent, a.position, Deriv1.Count
    FROM `menu` a
    LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1
    ON a.id = Deriv1.parent";

try
{

    $core = Core::getInstance();
    $stmt = $core->dbh->prepare($sql);
    $stmt->bindParam(':parent', $this->_mParent, PDO::PARAM_INT);

    if ($stmt->execute()) {

        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        echo "
";

        // Return a full array
        while ($menuItem = $stmt->fetch()) {
            $this->_aFull[] = array(
                'id' => $menuItem['id'],
                'label' => $menuItem['label'],
                'link' => $menuItem['link'],
                'parent' => $menuItem['parent'],
                'position' => $menuItem['position'],
                'Count' => $menuItem['Count'],
                );
        }

        // Return 3 arrays - 1 for each type of menu level
        foreach ($this->_aFull as $value) {
            if ($value['position'] == 0) {
                $this->_aLevel0[] = array(
                    'id' => $value['id'],
                    'label' => $value['label'],
                    'link' => $value['link'],
                    'parent' => $value['parent'],
                    'Count' => $value['Count'],
                    );
            }
            elseif ($value['position'] == 1) {
                $this->_aLevel1[] = array(
                    'id' => $value['id'],
                    'label' => $value['label'],
                    'link' => $value['link'],
                    'parent' => $value['parent'],
                    'Count' => $value['Count'],
                    );
            }
            elseif ($value['position'] == 2) {
                $this->_aLevel2[] = array(
                    'id' => $value['id'],
                    'label' => $value['label'],
                    'link' => $value['link'],
                    'parent' => $value['parent'],
                    'Count' => $value['Count'],
                    );
            }

        }

        // Evaluate the first level array (Array0) and echo the menu
        foreach ($this->_aLevel0 as $key => $value) {

            echo "<li class=\"dropdown\">
";
            echo "<a href=\"".$value['link']."\" class=\"dropdown-toggle js-activated\">".$value['label']."</a>
";

            // Evaluate the `Count` key of the array to see if the row being parsed has children
            if ($value['Count'] > 0) {

                echo "<ul class=\"dropdown-menu\">
";

                // For each children, echo the sub-menu
                foreach ($this->_aLevel1 as $a => $b) {
                    if ($b['parent'] == $value['id']) {
                        echo "<li><a href=\"".$value['link']."/".$b['link']."\">".$b['label']."</a></li>
";
                    }
                }

                echo "</ul>
";
            }

            echo "</li><!-- /.dropdown -->
";
        }
    }
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
?>

I'm sure it's not the most simple nor beautiful solution out there but at least it's working and only doing one query to the DB so it should be pretty fast in a production environment.

I left a 3rd array ready in case I need a 3rd level in my menus. All I have to do is add another counter to the query and another pair of IF/FOREACH to be able to echo that new menu level.