Multilevel menu (PHP/MySQL)

[sagstuen]

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

[firepages]

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.

[sagstuen]

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`)
)

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>";
                    }
                }
            }
        }
    }        
} 

[sagstuen]

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
  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:

<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...

[firepages]

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.