423,682 Members | 1,649 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

php/mysql dynamic multi-level menu problem

P: 8
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
Jan 25 '06 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 100+
P: 2,322
Basically you can't get the menu's to display the products under the right manufacturer?
Jan 25 '06 #2

Expert Mod 100+
P: 2,322
The problem was in your while statement. I moved the 2nd Query into the while statement and added the WHERE clause.

Try this code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. <? require("inc/connect.txt"); 
  3. /* Connecting to a database and retrieve data */ 
  4. $mysql_access = mysql_connect("localhost", "$un", "$pw") or die("Error connecting to database server: ".mysql_error()); 
  5. mysql_select_db($db, $mysql_access) or die("Error connecting to database: ".mysql_error());//always gotta do some error checking... 
  6. /* Get list of unique manufacturers */ 
  7. $result_manu = mysql_query("SELECT DISTINCT `Manufacturer` FROM `products`"); 
  8. # MOVE THIS INTO TO YOUR WHILE STATEMENT
  9. #/* Get list of products and details */ 
  10. #$result = mysql_query("SELECT `ProdID`,`Manufacturer`, `NameModel` FROM `products`"); 
  11. //always gotta do some error checking... 
  12. if (!$result) 
  13.   {exit("Error in SQL");} ?> 
  14. <!-- 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. --> 
  15. <ul id="nav"> 
  16.    <li> 
  17.     <a href="aboutus.php">Company</a> 
  18.       <ul> 
  19.          <li> 
  20.           <a href="index.php">Home Page</a> 
  21.          </li> 
  22.          <li> 
  23.           <a href="aboutus.php">About us</a> 
  24.          </li> 
  25.          <li> 
  26.           <a href="contactus.php">Contact us</a> 
  27.          </li> 
  28.       </ul> 
  29.    </li> 
  30.    <li> 
  31.     <a href="#">Products</a> 
  32.       <ul> 
  33.          <li> 
  34.           <a href="" class="daddy">By manufacturer</a> 
  35.              <ul> 
  36.               <? while ($resultset = mysql_fetch_assoc($result_manu)) 
  37.               $Manufacturer=$resultset['Manufacturer']; 
  38.                  echo "<li>"; 
  39.                   echo "<a href='' class='daddy'>$Manufacturer</a>"; 
  40.                      echo "<ul>";
  41.       # 2nd QUERY MOVED HERE
  42.       /* Get list of products and details */ 
  43.       $result = mysql_query("SELECT `ProdID`,`Manufacturer`, `NameModel` FROM `products` WHERE Manufacturer='$Manufacturer'"); 
  44.                       while ($resultset2 = mysql_fetch_assoc($result)) 
  45.                       $ProdID=$resultset2['ProdID']; 
  46.                       $NameModel=$resultset2['NameModel']; 
  47.                         echo "<li>"; 
  48.                          echo "<a href='products.php?ProdID=$ProdID'>$NameModel</a>"; 
  49.                         echo "</li>"; 
  50.                      echo "</ul>"; 
  51.                   echo "</li>"; 
  52. }?> 
  53.               </ul> 
  54.            </li> 
  55.         </ul> 
  56.     </li> 
  57. </ul> 
  58.  
If you run into errors; paste them here.
Jan 25 '06 #3

P: 8
Wow! Thanks! It works great! I'll go and study why it works now :D

Thanks again

pete
Jan 25 '06 #4

P: 1
That does work great, but how would one add a THIRD level?

I've got category as the main selector, and it lists each title under each category - but I've got subcategories defined as well..

Is this possble? Here's my code

Expand|Select|Wrap|Line Numbers
  1. $result_cat = mysql_query("SELECT DISTINCT `category` FROM navigation");
  2.  
  3. /*Begin Menu Navigation*/
  4. <div id="nav">
  5. <ul>
  6.     <? while ($resultset = mysql_fetch_assoc($result_cat)) {
  7.         $category=$resultset['category'];
  8.             echo "<li>";
  9.             echo "<a href='#' class='daddy'>$category</a>";
  10.                 echo "<ul>";
  11.  
  12. $result = mysql_query("SELECT `tutid`,`category`,`title`,`subcat` FROM navigation WHERE category='$category'");
  13.  
  14. ## Need a switch or If statement here, some of these secondary items will have tertiary items under them, how do I figure out if they do and then list them if they do? ##
  15.  
  16.     while ($resultset2 = mysql_fetch_assoc($result)) {
  17.     $tutid=$resultset2['tutid'];
  18.     $title=$resultset2['title'];
  19.         echo "<li>";
  20.         echo "<a href='tutorials.php?tutid=$tutid'>$Title</a>";                    echo "</li>"; }
  21.     echo "</ul>";
  22.     echo "</li>";
  23. }
  24.  
Mar 27 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.