MySQL RSS MySQL RSS

php/mysql dynamic multi-level menu problem

Reply

•
•
Join Date: Jan 2006
Posts: 8
Reputation: snowweb is an unknown quantity at this point 
Solved Threads: 0
snowweb snowweb is offline Offline
Newbie Poster

php/mysql dynamic multi-level menu problem

 
0
  #1
Jan 25th, 2006
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.

[PHP]
<? 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>
[/PHP]

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

<!-- 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>
[/HTML]

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

[HTML]

<!-- 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>
[/HTML]

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

kind regards

pete
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2006
Posts: 8
Reputation: snowweb is an unknown quantity at this point 
Solved Threads: 0
snowweb snowweb is offline Offline
Newbie Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #2
Jan 25th, 2006
Thanks, this is now resolved.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2007
Posts: 1
Reputation: SoccerDad is an unknown quantity at this point 
Solved Threads: 0
SoccerDad SoccerDad is offline Offline
Newbie Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #3
Jun 17th, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2006
Posts: 8
Reputation: snowweb is an unknown quantity at this point 
Solved Threads: 0
snowweb snowweb is offline Offline
Newbie Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #4
Feb 5th, 2008
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!
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 3,746
Reputation: nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough 
Solved Threads: 330
Moderator
Featured Poster
nav33n's Avatar
nav33n nav33n is offline Offline
Senior Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #5
Feb 5th, 2008
•
•
•
•
Originally Posted by snowweb View Post
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 ?
Ignorance is definitely not bliss!

*PM asking for help will be ignored*
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2004
Posts: 4,189
Reputation: peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of 
Solved Threads: 483
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Code tags enforcer

Re: php/mysql dynamic multi-level menu problem

 
0
  #6
Feb 6th, 2008
LOL, I'm actually supriced he replayed as the original post is 2 years old now
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 3,746
Reputation: nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough 
Solved Threads: 330
Moderator
Featured Poster
nav33n's Avatar
nav33n nav33n is offline Offline
Senior Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #7
Feb 6th, 2008
•
•
•
•
Originally Posted by peter_budo View Post
LOL, I'm actually supriced he replayed as the original post is 2 years old now
Prolly he came back today after a long break !
Ignorance is definitely not bliss!

*PM asking for help will be ignored*
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 1
Reputation: narasingh is an unknown quantity at this point 
Solved Threads: 0
narasingh narasingh is offline Offline
Newbie Poster

Re: php/mysql dynamic multi-level menu problem

 
0
  #8
Apr 3rd, 2009
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
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE IF NOT EXISTS `navi_links` (
  2.   `link_id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
  3.   `link_parent` SMALLINT(6) NOT NULL,
  4.   `link_url` VARCHAR(255) COLLATE latin1_general_ci NOT NULL,
  5.   `link_name` VARCHAR(255) COLLATE latin1_general_ci NOT NULL,
  6.   `display_order` SMALLINT(6) NOT NULL,
  7.   PRIMARY KEY  (`link_id`)
  8. ) 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);
Last edited by peter_budo; Apr 5th, 2009 at 4:45 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright court crm database design developer development distinct dui ec2 email enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC