Dynamic Unordered List

[chronister]

Hello PhpFreaks... been a while since I stopped by. I need to reach out for some assistance. The task I am trying to complete seems so simple, but I can't get my brain around it and have searched through these forums and the almighty google and have not found what I need. So here is what I need some directions on.

 

I am trying to create a multi-level un-ordered list using PHP & MySQL. It is going to be a database driven menu with an unknown number of parent items with 0 or more child items. Surely I am not the first one to need to solve this problem, but cannot find the simplest solution that gets me what I want.

 

Ok.. so I got a table setup like this with some test data in it....

 

CREATE TABLE IF NOT EXISTS `navigation` (
  `linkId` int(11) NOT NULL AUTO_INCREMENT,
  `displayName` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `parentId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`linkId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


INSERT INTO `navigation` (`linkId`, `displayName`, `url`, `parentId`) VALUES
(1, 'Home', '/', 0),
(2, 'City Hall', '/city_hall/', 0),
(3, 'City Council', '/city_council', 0),
(4, 'Upcoming Meetings', '/city_council/upcoming_meetings.php', 3),
(5, 'Past Minutes', '/city_council/past_minutes', 3),
(6, 'Council Members', '/city_council/council_members.php', 3),
(7, 'Forms & Downloads', '/downloads', 0),
(8, 'Parks & Recreation', '/parks', 0),
(9, 'Park Name', '/parks/park.php', ,
(10, 'Another Link', '/parks/link2.php', ;

 

I have a query and loop that grabs the entire menu, and places it in 2 arrays to separate the top-level parent items and the items that have a parent id != 0.

 

$items = $db->getData("SELECT linkId, displayName, url, parentId FROM navigation ORDER BY parentId, linkId ASC");
while ($row = mysql_fetch_object($items['result'])) {
    if ($row->parentId == 0) {
        $parent_menu[$row->linkId]['label'] = $row->displayName;
        $parent_menu[$row->linkId]['link'] = $row->url;
    } else {
        $sub_menu[$row->linkId]['parent'] = $row->parentId;
        $sub_menu[$row->linkId]['label'] = $row->displayName;
        $sub_menu[$row->linkId]['link'] = $row->url;
    }
}

 

That gives me an array that looks like this.

 

Array(
    [1] => Array (
            [label] => Home
            [link] => /
        )

    [2] => Array (
            [label] => City Hall
            [link] => /city_hall/
        )

    [3] => Array(
            [label] => City Council
            [link] => /city_council
        )

    [7] => Array(
            [label] => Forms & Downloads
            [link] => /downloads
        )

    [8] => Array(
            [label] => Parks & Recreation
            [link] => /parks
        )

)

Array
(
    [4] => Array(
            [parent] => 3
            [label] => Upcoming Meetings
            [link] => /city_council/upcoming_meetings.php
        )

    [5] => Array(
            [parent] => 3
            [label] => Past Minutes
            [link] => /city_council/past_minutes
        )

    [6] => Array(
            [parent] => 3
            [label] => Council Members
            [link] => /city_council/council_members.php
        )

    [9] => Array(
            [parent] => 8
            [label] => Park Name
            [link] => /parks/park.php
        )

    [10] => Array(
            [parent] => 8
            [label] => Another Link
            [link] => /parks/link2.php
        )
)

 

Taking that array and doing a couple loops, gets me close (sorta.. I realize that there needs to be recursion for this to work).

 

echo '<ul>'."\r\n";
foreach($parent_menu as $pid => $pvalue){
echo '  <li><a href="'.$pvalue['link'].'">'.$pvalue['label'].'</a>'."\r\n";

	foreach($sub_menu as $sid => $svalue){
		if($svalue['parent'] == $pid){
			echo '    <li>'.$svalue['label'].'</li>'."\r\n";
		}
	}

echo '</li>'."\r\n";
}
echo '</ul>'."\r\n";

 

That gets me a list that is not quite correct markup. The nested <ul> items are missing because I could not determine the best way to do this.

 

The goal for the data presented here is to be marked up like this.

 

<ul>
  <li><a href="/">Home</a></li>
  <li><a href="/city_hall">City Hall</a></li>
  <li><a href="/city_council">City Council</a>
    <ul>
      <li><a href="/city_council/upcoming_meetings.php">Upcoming Meetings</a></li>
      <li><a href="/city_council/past_minutes">Past Minutes</a></li>
      <li><a href="/city_council/council_members.php">Council Members</a></li>
    </ul>
  </li>
  <li><a href="/downloads">Forms & Downloads</a></li>
  <li><a href="/parks">Parks & Recreation</a>
    <ul>
      <li><a href="/parks/park.php">Park 1</a></li>
      <li><a href="/parks/link2.php">Another Link</a></li>
    </ul>
  </li>
</ul>

 

 

Thats where I am at here. Any ideas or tips would be helpful and appreciated. So anyone solved this problem before? Sorry for the long post, but I wanted to give all information and code that I have.

 

Thanks in advance,

 

Nathan

[chronister]

As a side note, I am aware of how to do this with multiple calls to the database to build the menu, but I would like to maximize performance and do 1 DB call, and then use logic to get the data out of the array like I want. So If I cannot get the second solution to work, then I will go with multiple database calls which makes me shudder.

[chronister]

Hi All,

 

Still needing assistance on the array logic for this task. But I would like your opinions on this code. It does exactly what I want making a database call for each menu item to determine if there are child items.

 

function buildNavigation($parentid, $db) {
$items = $db->getData("SELECT linkId, displayName, url, parentId FROM navigation WHERE parentId = $parentid");
if($items['numRows'] > 0){
	echo "\r\n".'<ul>'."\r\n";
		while($row = mysql_fetch_object($items['result'])) {
		echo '  <li>';
		echo '<a href="'.$row->url.'">'.$row->displayName.'</a>';
		buildNavigation($row->linkId, $db);
		echo '</li>'."\r\n";

		}

	echo '</ul>'."\r\n";
}
}

 

Do you folks this is an acceptable way to do it? Or is this potentially too heavy on database calls? I have tried to take some of the logic in this function and apply it to the array processing logic and am not having any luck.

 

Thanks in advance.

 

[chronister]

Well, I got it solved and working like I want with 1 single database call.

 

Kinda disappointed that NO ONE had any input or assistance here.  Seems like I can't get help on here for nothing the past several times i posted looking for assistance. Am I asking the questions in the wrong way, or are the questions just too difficult for the average PHP freaks user? I know there are some REALLY smart and talented folks on here whom I respect very much. People who are WAY smarter at this stuff than I could ever hope to be.

 

I understand people donate their time here. I have donated many hours on here trying to help others myself. 

 

But I would like some feedback as to why my questions go unanswered. I try and provide all information so the problem is clear... is that it? Too much information?

 

Any input on the second part would be appreciated.

 

Thanks,

 

Nathan

[PaulRyan]

Hello Chronister.

 

These situations do arise within these types or forums, it has most likely got nothing to do with you or the questions you are asking.

Your thread might have been overlooked by the regulars, it happens, I had to wait 3 days for a reply to one of my questions, people are usally helping the real n00bs with their questions, some people may have wanted to help but didn't have the time when they saw your thread.

 

It's understandable with your frustrations when you question doesn't get answered, just persevere keep trying to do it yourself (like you managed too) you'll feel better that you managed to fix your problem with outside help.

 

Hopefully in future I will see a thread of yours and help you a.s.a.p.

 

But until then, best regards, Paul.