Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Question Unanswered: php/mysql dynamic multi-level menu problem

    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 Code:
    <? 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.
    HTML Code:
    <!-- 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..

    HTML Code:
    <!-- 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

  2. #2
    Join Date
    Jan 2006
    Posts
    13
    This is now resolved. Thanks

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Just to help others, who may have a similar problem or user requirement - how did you solve your problem?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2006
    Posts
    13
    Hi Mark,

    Good point.. I did it like this...

    PHP Code:
    <? 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`"); 
    # MOVE THIS INTO TO YOUR WHILE STATEMENT
    #/* Get list of products and details */ 
    #$result = mysql_query("SELECT `ProdID`,`Manufacturer`, `NameModel` FROM `products`"); 
    //always gotta do some error checking... 
    if (!$result_manu
      {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>";
          
    # 2nd QUERY MOVED HERE
          /* Get list of products and details */ 
          
    $result mysql_query("SELECT `ProdID`,`Manufacturer`, `NameModel` FROM `products` WHERE Manufacturer='$Manufacturer'"); 
                          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>
    Probably not the best way in the world but it seems to work.

    Regards, pete

  5. #5
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    If you're still interested, one way to avoid the nested queries is to select all the products and group them by manufacturer and use the GROUP_CONCAT function in your select clause to turn the product results into a list.

    Then...in your php code, do something like this:
    Code:
    //pseudo-code
    foreach record in resultset
    {
       //write out info
       foreach product in explode(record, ",") {
          //write out info
       }
    }
    You're going to need nested loops anyways, but this will at least eliminate the reduntant calls to the database.

  6. #6
    Join Date
    Jan 2006
    Posts
    13
    Thanks, JFulton. I've actually sorted this now and it is working nicely (I don't know about 'efficiently'! yet, since I don't know how to determine that yet).

    However, being a neat and tidy person, I would like my code to be as compact as possible and will therefore study your method and see if I can get my head around it after I have finished the major site content and functionalily.

    Thanks for you help

    peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •