php/mysql dynamic multi-level menu problem

Question

snowweb 0 Newbie Poster

18 Years Ago

I am trying to implement a CSS hierarchical unfolding menu on a site. The thing is, it needs to be dynamically populated from the results of a database query. I previously had the menu working but then it was ‘hard coded’ and not built on the fly.

Menu description:

2 top level items “Company and “Products (we will ignore “Company since it is still hard coded and not causing a problem.

Below “Products we have hard coded “By Manufacturer. So you hover over “Products and it unfolds and “By Manufacturer is visible. If you hover over that, a list of manufacturers should open to the right. This list is extracted from the data base using SELECT DISTINCT.

Then if you hover over a manufacturer, another level unfolds which should contain the products of that manufacturer.

My problem is that the list of products includes all products in the database, not just those from the relevant manufacturer. The menus of the other manufacturers are empty.

The manufacturer under which all the products appear, is the manufacturer of the first product in the database.

<? require("inc/connect.txt"); 
/* Connecting to a database and retrieve data */
$mysql_access = mysql_connect("localhost", "$un", "$pw") or die("Error connecting to database server: ".mysql_error());
mysql_select_db($db, $mysql_access) or die("Error connecting to database: ".mysql_error());//always gotta do some error checking...

/* Get list of unique manufacturers */
$result_manu = mysql_query("SELECT DISTINCT `Manufacturer` FROM `products`"); 

/* Get list of products and details */
$result = mysql_query("SELECT `ProdID`,`Manufacturer`, `NameModel` FROM `products`"); 

//always gotta do some error checking...
if (!$result) 
  {exit("Error in SQL");} ?>

<!-- Begin unfolding menu code/structure when viewed without the accompanying stylesheet, it should display as a multi-level list of links which makes it easy to see which category items are in. -->

<ul id="nav">

   <li>
    <a href="aboutus.php">Company</a>
      <ul>
         <li>
          <a href="index.php">Home Page</a>
         </li>
         <li>
          <a href="aboutus.php">About us</a>
         </li>
         <li>
          <a href="contactus.php">Contact us</a>
         </li>
      </ul>
   </li>

   <li>
    <a href="#">Products</a>
      <ul>
         <li>
          <a href="" class="daddy">By manufacturer</a>
             <ul>
              <? while ($resultset = mysql_fetch_assoc($result_manu))

{ 
              $Manufacturer=$resultset['Manufacturer']; 
                 echo "<li>";
                  echo "<a href='' class='daddy'>$Manufacturer</a>";
                     echo "<ul>";
                      while ($resultset2 = mysql_fetch_assoc($result))
{
                      $ProdID=$resultset2['ProdID'];
                      $NameModel=$resultset2['NameModel'];
                        echo "<li>";
                         echo "<a href='products.php?ProdID=$ProdID'>$NameModel</a>";
                        echo "</li>";
}
                     echo "</ul>";
                  echo "</li>";
}?>
              </ul>
           </li>
        </ul>
    </li>
</ul>

Below is the code which is rendered by the browser when displayed without the appropriate CSS stylesheet and JavaScript that makes it work.

<!-- Begin unfolding menu code/structure when viewed without the accompanying stylesheet, it should display as a multi-level list of links which makes it easy to see which category items are in. -->

<ul id="nav">

   <li>
    <a href="aboutus.php">Company</a>
      <ul>
         <li>
          <a href="index.php">Home Page</a>
         </li>
         <li>
          <a href="aboutus.php">About us</a>
         </li>
         <li>
          <a href="contactus.php">Contact us</a>
         </li>
      </ul>
   </li>

   <li>
    <a href="#">Products</a>
      <ul>
         <li>
          <a href="" class="daddy">By manufacturer</a>
             <ul>
                <li><a href='' class='daddy'>Kenwood</a>
                   <ul>
                      <li>
                       <a href='products.php?ProdID=1'>TK-270G/370G</a>
                      </li>
                      <li>
                       <a href='products.php?ProdID=4'>tester</a>
                      </li>
                      <li>
                       <a href='products.php?ProdID=5'>Chef</a>
                      </li>
                   </ul>
                </li>
                <li><a href='' class='daddy'>test manufacturer</a>
                   <ul>
                   </ul>
                </li>
             </ul>
           </li>
        </ul>
    </li>
</ul>

...and this is the code that I am aiming to have rendered..

<!-- Begin unfolding menu code/structure when viewed without the accompanying stylesheet, it should display as a multi-level list of links which makes it easy to see which category items are in. -->

<ul id="nav">

   <li>
    <a href="aboutus.php">Company</a>
      <ul>
         <li>
          <a href="index.php">Home Page</a>
         </li>
         <li>
          <a href="aboutus.php">About us</a>
         </li>
         <li>
          <a href="contactus.php">Contact us</a>
         </li>
      </ul>
   </li>

   <li>
    <a href="#">Products</a>
      <ul>
         <li>
          <a href="" class="daddy">By manufacturer</a>
             <ul>
                <li><a href='' class='daddy'>Kenwood</a>
                   <ul>
                      <li>
                       <a href='products.php?ProdID=1'>TK-270G/370G</a>
                      </li>
                      <li>
                       <a href='products.php?ProdID=5'>Chef</a>
                      </li>
                   </ul>
                </li>
                <li><a href='' class='daddy'>test manufacturer</a>
                   <ul>
                      <li>
                       <a href='products.php?ProdID=4'>tester</a>
                      </li>
                   </ul>
                </li>
             </ul>
           </li>
        </ul>
    </li>
</ul>

I've a feeling that I'm close. Can anyone see my mistake please?

kind regards

pete

mysql php

9 Contributors
12 Replies
2K Views
5 Years Discussion Span
Latest Post 13 Years Ago Latest Post by janajay

SoccerDad 0 Newbie Poster

17 Years Ago

Just for anyone else who happens upon this thread and are as irritated as I am with folks who post "It's Fixed", but don't have the courtesy to say how they fixed it, the original poster appears to have gotten "it fixed" over here:

http://www.thescripts.com/forum/thread449155.html

nav33n 472 Purple hazed!

16 Years Ago

and why should I waste my time explaining how I fixed it when NO-ONE on this forum gave me even the slightest help?
- Courtesy works both ways... not just one!

Well, people might not be knowing the answer for your question. You can't expect them to know the answer for everything. Can you ? And If you had posted your 'fix', it would have helped others who might have encountered the same problem. If your attitude is, 'why should I waste my time explaining blah blah blah', why do you expect help from others ?

peter_budo 2,532 Code tags enforcer

16 Years Ago

LOL, I'm actually supriced he replayed as the original post is 2 years old now

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

snowweb 0 Newbie Poster · Answer 1 · 2006-01-25T12:01:07+00:00

snowweb 0 Newbie Poster

18 Years Ago

Thanks, this is now resolved.

snowweb 0 Newbie Poster · Answer 2 · 2008-02-06T05:41:47+00:00

and why should I waste my time explaining how I fixed it when NO-ONE on this forum gave me even the slightest help?

- Courtesy works both ways... not just one!

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 3 · 2008-02-06T15:38:33+00:00

LOL, I'm actually supriced he replayed as the original post is 2 years old now

:P Prolly he came back today after a long break !

narasingh 0 Newbie Poster · Answer 4 · 2009-04-03T19:08:19+00:00

We can create multi level drop down using php/mysql .
we can also create up to unlimited level of drop down using this code.

first i am creating a mysql table

CREATE TABLE IF NOT EXISTS `navi_links` (
  `link_id` smallint(6) NOT NULL auto_increment,
  `link_parent` smallint(6) NOT NULL,
  `link_url` varchar(255) collate latin1_general_ci NOT NULL,
  `link_name` varchar(255) collate latin1_general_ci NOT NULL,
  `display_order` smallint(6) NOT NULL,
  PRIMARY KEY  (`link_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=11 ;

now i am creating one recursive function which i will call number of times till the parent exist.

// prepare special array with parent-child relations
$menuData = array(
    'items' => array(),
    'parents' => array()
);
$result = mysql_query("SELECT link_id id, link_parent parentId, link_name name FROM navi_links  ORDER BY link_parent"); 

while ($menuItem = mysql_fetch_assoc($result))
{
    $menuData['items'][$menuItem['id']] = $menuItem;
    $menuData['parents'][$menuItem['parentId']][] = $menuItem['id'];
}

// menu builder function, parentId 0 is the root
function buildMenu($parentId, $menuData)
{
    $html = '';
	$parent='';

    if (isset($menuData['parents'][$parentId]))
    {
		$menuClass= ($parentId==0) ? ' class="menu" id="menu"' : '';
		$parent= ($parentId==0) ? 0 : 1;	
        $html = "<ul{$menuClass}>\n";
				
        foreach ($menuData['parents'][$parentId] as $itemId)
        {
			//subment
			$result=mysql_query("select * from navi_links where link_parent='$itemId'");
			if (mysql_num_rows($result)>(int)0 && $parentId!=0) {
                $subm  =' class="sub"';
        	}else{
			   $subm  ='';
			}
			//end
			
			$menu = $parentId == 0 ? ' class="menulink"' : '';		//class of main menu
            $html .= '<li>' . "<a{$subm}{$menu} href=\"#\" title=\"{$row['title']}\">{$menuData['items'][$itemId]['name']}</a>";
			
            // find childitems recursively
            $html .= buildMenu($itemId, $menuData);

            $html .= '</li>';
        }
        $html .= '</ul>';
    }

    return $html;
}

// output the menu
echo buildMenu(0, $menuData);

kodegeek 0 Newbie Poster · Answer 5 · 2010-03-28T18:23:16+00:00

Perfect!

We can create multi level drop down using php/mysql .
we can also create up to unlimited level of drop down using this code.

first i am creating a mysql table

CREATE TABLE IF NOT EXISTS `navi_links` (
  `link_id` smallint(6) NOT NULL auto_increment,
  `link_parent` smallint(6) NOT NULL,
  `link_url` varchar(255) collate latin1_general_ci NOT NULL,
  `link_name` varchar(255) collate latin1_general_ci NOT NULL,
  `display_order` smallint(6) NOT NULL,
  PRIMARY KEY  (`link_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=11 ;

now i am creating one recursive function which i will call number of times till the parent exist.

// prepare special array with parent-child relations
$menuData = array(
    'items' => array(),
    'parents' => array()
);
$result = mysql_query("SELECT link_id id, link_parent parentId, link_name name FROM navi_links  ORDER BY link_parent"); 

while ($menuItem = mysql_fetch_assoc($result))
{
    $menuData['items'][$menuItem['id']] = $menuItem;
    $menuData['parents'][$menuItem['parentId']][] = $menuItem['id'];
}

// menu builder function, parentId 0 is the root
function buildMenu($parentId, $menuData)
{
    $html = '';
	$parent='';

    if (isset($menuData['parents'][$parentId]))
    {
		$menuClass= ($parentId==0) ? ' class="menu" id="menu"' : '';
		$parent= ($parentId==0) ? 0 : 1;	
        $html = "<ul{$menuClass}>\n";
				
        foreach ($menuData['parents'][$parentId] as $itemId)
        {
			//subment
			$result=mysql_query("select * from navi_links where link_parent='$itemId'");
			if (mysql_num_rows($result)>(int)0 && $parentId!=0) {
                $subm  =' class="sub"';
        	}else{
			   $subm  ='';
			}
			//end
			
			$menu = $parentId == 0 ? ' class="menulink"' : '';		//class of main menu
            $html .= '<li>' . "<a{$subm}{$menu} href=\"#\" title=\"{$row['title']}\">{$menuData['items'][$itemId]['name']}</a>";
			
            // find childitems recursively
            $html .= buildMenu($itemId, $menuData);

            $html .= '</li>';
        }
        $html .= '</ul>';
    }

    return $html;
}

// output the menu
echo buildMenu(0, $menuData);

extendstudio 0 Newbie Poster · Answer 6 · 2010-05-18T14:29:26+00:00

If you want to see a tutorial on how to build a dynamic php menu in Dreamweaver without coding you can see it here <snipped>

alex71 0 Newbie Poster · Answer 7 · 2010-09-11T03:30:57+00:00

alex71 0 Newbie Poster

14 Years Ago

<snipped> looks quite great! Thankx : )

janajay 0 Newbie Poster · Answer 8 · 2011-02-24T20:22:39+00:00

its very super but dont no how to set the links for menus and sub menus from database

janajay 0 Newbie Poster · Answer 9 · 2011-02-24T20:24:26+00:00

its very super but dont no how to set the links for menus and sub menus from database

please help me in this