Web Analytics Made Easy -
StatCounter Multilevel menu (PHP/MySQL) - CodingForum

Announcement

Collapse
No announcement yet.

Multilevel menu (PHP/MySQL)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multilevel menu (PHP/MySQL)

    I'm doing a webshop project at the time. It's sort of a "learn-php-and-mysql"-project, meaning I'm not very into php yet...

    What I'm having trouble figuring out is this...

    I have a table in mysql whit the following fields:
    group_id - INT PRIMARY AUTOINCREMENT
    group_name VARCHAR(64)
    group_parent - INT

    To illustrate the table, let's say I have the following rows in the table:
    1 - Hardware - 0
    2 - Software - 0
    3 - Motherboards - 1
    4 - Network - 1
    5 - Operatingsystems - 2
    6 - Socket A - 3
    7 - Socket 478 - 3
    8 - Albatron - 6
    9 - Albatron - 7
    --->

    The menu it self would be a tree view like this:
    Hardware
    - Motherboards
    --- Socket A
    ----- Albatron
    --- Socket 478
    ----- Albatron
    - Network
    Software
    - Operatingsystems

    Currently I've coded this by usinbg nested loops in a three-level arcitecture... Thats NOT what I want...
    What I want is an unlimited level iterational loop, so I can put in the levels as it suits me and my needs.

    My programming knowledge is farely simple at the time, so please make things a clear as possible...

    Regards and thanks in advance...
    Roger Jensen
    Norway

  • #2
    I would be more inclined to sort out your database structure.

    3 tables would may better say Categories , SubCategories and Data

    cat
    ..................
    int id
    varchar name

    subcat
    ......................
    int id
    int cat_id
    varchar name

    data
    ....................
    int id
    int cat_id
    int sub_cat_id
    varchar name
    varchar desc

    now a typical data entry would look like ..
    id cat_id subcat_id name desc
    1 2 3 asrock blahdeblah

    where '2' related to id 2 in the cat table & 3 related to id in the sub_cat table etc.

    Yes there will now be more queries to perform to generate your menu but your main query will be far more efficient and you can sort & order by category,subcategory etc.

    Sorry I did not answer your question as such but you really need to sort out the database now before you run into trouble later.

    Note I am not suggesting that this is the correct or best structure for your particular problem , its just 1 way of doing it.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

    Comment


    • #3
      I don't know if I understand you correctly.... I've got some clues in other forums saying I need a recursing function of some sort....

      As for today I use nested loops, and the following table..

      TABLE `t_gruppeliste` (
      `gruppe_id` int(11) NOT NULL auto_increment, // = group_id
      `gruppe_navn` varchar(30) NOT NULL default '', // = group_name
      `gruppe_forelder` int(11) NOT NULL default '0', // = group_parent
      `gruppe_url` varchar(255) NOT NULL default '',
      `gruppe_show` tinyint(1) NOT NULL default '1',
      PRIMARY KEY (`gruppe_id`),
      FULLTEXT KEY `gruppe_navn` (`gruppe_navn`)
      )

      PHP Code:
      if (!($connection = @ mysql_pconnect ($sql_server$sql_user$sql_pwd))) showerror(); 
      if (!(
      mysql_select_db ($sql_db$connection))) showerror();
      $gruppe_query1 "SELECT * FROM t_gruppeliste WHERE gruppe_forelder = 0 AND gruppe_show = 1 ORDER BY gruppe_navn";

      if (
      $gruppe_result1 = @ mysql_query ($gruppe_query1$connection))
      {
          while (
      $gruppe_row1 mysql_fetch_array($gruppe_result1))
          {
          echo 
      "<TR>";
          echo 
      "<TD CLASS='menu' STYLE=font-size:8pt; font-family:verdana; text-align:left;'>";
          echo 
      "  <a class='menu' href='index.php?page=prodliste&gruppe=" $gruppe_row1['gruppe_id'] . "'>";
          echo 
      "  <p align='left' STYLE='text-indent: 0; font-weight: bold'>" $gruppe_row1['gruppe_navn'];
          echo 
      "</a>";
          echo 
      "</TD>";
          echo 
      "</TR>";

          
      $gruppe_query2 "SELECT * FROM t_gruppeliste WHERE gruppe_forelder = " $gruppe_row1['gruppe_id'] . " AND gruppe_show = 1  ORDER BY gruppe_navn";
              if (
      $gruppe_result2 = @ mysql_query ($gruppe_query2$connection))
              {
                  while ((
      $gruppe_row2 mysql_fetch_array($gruppe_result2)) && ($gruppe_row2 == TRUE))
                  {
                  echo 
      "<TR>";
                  echo 
      "<TD CLASS='menu' STYLE='font-size:8pt; font-family:verdana; text-align:left;'>";
                  echo 
      "  <a class='menu' href='index.php?page=prodliste&gruppe=" $gruppe_row2['gruppe_id'] . "'>";
                  echo 
      "  <p align='left' STYLE='text-indent: 0;'>* " $gruppe_row2['gruppe_navn'];
                  echo 
      "</a>";
                  echo 
      "</TD>";
                  echo 
      "</TR>";
                  
      $gruppe_query3 "SELECT * FROM t_gruppeliste WHERE gruppe_forelder = " $gruppe_row2['gruppe_id'] . " AND gruppe_show = 1  ORDER BY gruppe_navn";
                     
                      if (
      $gruppe_result3 = @ mysql_query ($gruppe_query3$connection))
                      {
                          while (
      $gruppe_row3 mysql_fetch_array($gruppe_result3))
                          {
                              echo 
      "<TR>";
                              echo 
      "<TD CLASS='menu' STYLE='font-size:8pt; font-family:verdana; text-align:left;'>";
                              echo 
      "  <a class='menu' href='index.php?page=prodliste&gruppe=" $gruppe_row3['gruppe_id'] . "'>";
                              echo 
      "  <p align='left' STYLE='text-indent: 10;'>- " $gruppe_row3['gruppe_navn'];
                              echo 
      "</a>";
                              echo 
      "</TD>";
                              echo 
      "</TR>";
                          }
                      }
                  }
              }
          }        

      Comment


      • #4
        SOLVED Recursive multilevel menu (PHP/MySQL)

        I solved thisone myself.

        As my knowledge of PHP is somewhat intermediate, I had some trouble figuring out this recusion thing, but I'm learning. This is what I did, and it works just fine for me... Hope it helps some of you out there.

        What it does is this:

        I have a table who stores my menu-items or group-names as I like to call them. The following table should be self-explaining:

        CREATE TABLE `t_groups` (
        `groupe_id` int(11) NOT NULL auto_increment,
        `groupe_name` varchar(30) NOT NULL default '',
        `groupe_parent` int(11) NOT NULL default '0',
        `groupe_url` varchar(255) NOT NULL default '',
        `groupe_show` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (`groupe_id`),
        FULLTEXT KEY `groupe_navn` (`groupe_navn`)
        )


        I'm using a style-sheet to format the table-cells...
        The following function does the dirty work:
        PHP Code:
        <?php
          
        function menu_level($parent$indent$connection)
          {
            if (isset(
        $parent))
            {
              
        $query "SELECT * FROM t_groups WHERE group_parent = $parent  AND group_show = 1 ORDER BY group_name";
            }

            if (!(
        $result = @ mysql_query ($query$connection)))
              
        showerror();
            else
            {
              while (
        $row mysql_fetch_array($result))
              {
                echo 
        "<TR>";
                echo 
        "  <TD CLASS='menu' STYLE=font-size:8pt; font-family:verdana; text-align:left;'>";
                echo 
        "    <a class='menu' href='index.php?page=prodliste&group=" $row['group_id'] . "'>";
                echo 
        "      <p align='left' STYLE='text-indent:  $indent ; font-weight: bold'>" $row['group_name'];
                echo 
        "    </a>";
                echo 
        "  </TD>";
                echo 
        "</TR>";

                
        $sub_query "SELECT * FROM t_groups WHERE group_parent = " $row['group_id'] . " AND group_show = 1 LIMIT 0, 1";
                if (
        $sub_result = @ mysql_query ($sub_query$connection))
                {
                  
        $sub_indent $indent 10;
                  
        menu_level($row['group_id'], $sub_indent$connection);
                }
              }
            }
          }
        ?>
        This is the code going into the actual page where the menu is supposed to apear:

        PHP Code:
        <TABLE CLASS='block' STYLE="vertical-align:top">
        <TR>
          <TD CLASS='heading'>GROUPS
          </TD>
        </TR>
        <?php
          
        if (!($connection = @ mysql_pconnect ($sql_server$sql_user$sql_pwd)))
            
        showerror();
          if (!(
        mysql_select_db ($sql_db$connection)))
            
        showerror();
          
        menu_level(0,0,$connection);
        ?>
        </TABLE>
        I hope this is of some use... Feel free to comment me on this one...

        Comment


        • #5
          Hi , my point was not to look into nested / recursive loops or indeed dancing through hoops which is what you end up doing if your database structure is not right.

          I can see you have worked something out there,& thats excellent , but you need to revisit your database structure now before your database gets big & slow and your queries & their parsing become unrealistic.

          Basically if the DB is right then you should never need to be doing what you are doing in your code below.
          resistance is...

          MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

          Comment

          Working...
          X