• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Building nested menu's from SQL database...

Howdy all!

I am working on a site right now, and they have asked me to make their products catagorisable, and for each catagory to be catagorisable (tongue twisted there) so I have set up a database with a table like this (each product references a CatagoryID):

Catagories:
CatagoryID Int(10)
ParentCatagory(10) default "0"
CatagoryName varchar(255)

Now, the Catagories is what i am having issues with. I set 0 to be the root catagory (though it doesn't exist in the database, I use it in the code).

Here is some sample data:
1        0        Tables
2       1       Coffee Tables
3       1       Bedside Tables
4       0       Cabinets
5       4       Chest of Drawers
6       2       Books about coffee tables
7       3       Lamps n stuff
8       4       Chest of Drawers


What the client wants (for asthetic state) is to only have the subcatagories of the current selected catagory visible in the menu.
<?
      if(count($_GET['GalleryID']) != 0)
      {
            $TRACER_SQL_STATEMENT = "SELECT CatagoryParent FROM catagories WHERE CatagoryID = {$_GET['CatagoryID']}" ;
            $TRACER_SQL_RESULT = mysql_query($TRACER_SQL_STATEMENT) ;
            $TRACER_SQL_LINE = mysql_fetch_array($TRACER_SQL_RESULT) ;
            $COUNTER = 1 ;
            $TRACER_ARRAY[0] = $_GET['GalleryID'] ;
            $TRACER_ARRAY[1] = $TRACER_SQL_LINE['CatagoryParent'] ;
            while($TRACER_ARRAY[$COUNTER] != 0)
            {
                  $TRACER_SQL_STATEMENT = "SELECT CatagoryParent FROM catagories WHERE CatagoryID = {$TRACER_ARRAY[$COUNTER]}" ;
                  $TRACER_SQL_RESULT = mysql_query($TRACER_SQL_STATEMENT) ;
                  $TRACER_SQL_LINE = mysql_fetch_array($TRACER_SQL_RESULT) ;

                  $COUNTER++ ;
                  $TRACER_ARRAY[$COUNTER] = $TRACER_SQL_LINE['CatagoryParent'] ;
            }
            mysql_free_result($TRACER_SQL_RESULT);
      }
?>

This gets me, from the current catagory down to the root catagory, the parent ID of each parent of the subcatagories (that sounds like double dutch, but I hope it makes sense)

Now I am stuck as to how to actually BUILD the menu... I started, but then realised I was working in an bottom-about-pete way... the menu should look something like this:

root
|------ Tables (visible)
|          |------- Coffee Tables (Currently selected, via index.php?CatagoryID=X)
|          |            |------ Books about coffee tables ;) (visible)
|          |------- Bedside Tables (not visible)
|          |            |------ Lamps n Stuff (not visible)
|------ Cabinets (visible)
            |------- Chest of Drawers(not visible)

And so on... so only the ones DIRECTLY under root are visible, and only under the current selected menu are visible... I don't know if this is possible...

Infact, thinking about it, reguardless of if they are visible or not, I still do not know how to do nested menus like this... the code above is only to help me pick which ones should be visible, I might be able to work it out if someone can help me with the actual nested menus coming from the database. In the menu, only the catagories need to be displayed, the products need not be...

Thanks
Zak
0
Zak_MakeWithTheFunny
Asked:
Zak_MakeWithTheFunny
  • 3
  • 3
  • 2
1 Solution
 
minichickenCommented:
I think what you looking for is a recursive function, as suggested in a PAQ {Q_21180002.html} similar if not the same situation.

<?php
function Do_Query($parentid) {
 $Res = mysql_query("SELECT * FROM Categories WHERE parentid=$parentid ORDER BY postdate ASC");
 while ($Tab = mysql_fetch_assoc($Res)) {
  echo ($parentid == '0' ? '<LI>' : '<UL>');

  /* Print all the fields you want here ... */
  echo $Tab['from'];
  // echo ...
  /* -------------------------------------- */

  // Now the function calls itself !
  Do_Query($Tab['id']);
  echo ($parentid == '0' ? '</LI>' : '</UL>');
 }
 mysql_free_result($Res);
}
 
// Call function for first level : parentid=0
Do_Query('0');
?>
0
 
minichickenCommented:
I think this is more correct:

*****************************************************************************

<?php

$username="****";
$password="****";
$database="****";

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die(mysql_error());

function Do_Query($parentid) {
 $Res = mysql_query("SELECT * FROM Catagories WHERE ParentCatagory = '$parentid'");
 while ($Tab = mysql_fetch_assoc($Res)) {
  echo ($parentid == '0' ? '<LI>' : '<UL>');

  /* Print all the fields you want here ... */
  echo $Tab['CatagoryName'];
  // echo ...
  /* -------------------------------------- */

  // Now the function calls itself !
  Do_Query($Tab['CatagoryID']);
  echo ($parentid == '0' ? '</LI>' : '</UL>');
 }
 mysql_free_result($Res);
}
 
// Call function for first level : parentid=0
Do_Query('0');
?>
0
 
minichickenCommented:
The recursive function was extract from http:Q_21180002.html 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hernst42Commented:
To display only the root and the path to the current element do it this way:

First get the top most root-element of the current category. Then display all root-elements and when you reach the matching top element for the current cat display that path. In pseudo-code it wolud look like:

list($topElement, $levels) = getRootOf($catid);
foreach ($rootElements as $element) {
  echo displayElement($element);
  if ($element == $topElement) {
     echo displayPath($catid, $level, $levels);
   }
}

function displayPath($id, &$level, $levels) {
    $levels++;
    $Pid = getParentId($id);
    if ($Pid == 0) {
        // is already a root element
        return '';
    }
    return displayPath($Pid, $level, $levels) . displayElement($id, $levels - $level);
}

function displayElement($id, $level) {
   // get info of the database for that id
   $result = str_repeat('|        ',  $level -1);
   $result .= ' |------- ' . $description;
}

function getRootOf($id) {
    $COUNTER = 0;
    $TRACER_ARRAY[$COUNTER] = $id;
    while($TRACER_ARRAY[$COUNTER] != 0) {
        $TRACER_SQL_STATEMENT = "SELECT CatagoryParent FROM catagories WHERE CatagoryID = {$TRACER_ARRAY[$COUNTER]}" ;
        $TRACER_SQL_RESULT = mysql_query($TRACER_SQL_STATEMENT) ;
        $TRACER_SQL_LINE = mysql_fetch_array($TRACER_SQL_RESULT) ;
        $COUNTER++ ;
        $TRACER_ARRAY[$COUNTER] = $TRACER_SQL_LINE['CatagoryParent'] ;
    }
     return array($TRACER_ARRAY[$COUNTER-1],  $COUNTER);
}
0
 
Zak_MakeWithTheFunnyAuthor Commented:
Okay, I fail to see how that will still help me create the menu... or how it returns any different from what I already have...

And the |---- was only to give an idea of how it is structured, not literal...

It would be built using List Items (<li>'s)

How would I call these functions? The actual code for building the menu, not getting these parent ID's and what not, is the issue I am having... I successfully did that with my first peice of code. It is the building of the menu (because there can be an infinite depth on the subcatagories) that I am not sure how to do...

Here is the code I started to write...

      //This grabs the parent menu lists.
      $PARENT_MENU_SQL_STATEMENT = "SELECT * FROM family_galleries WHERE GalleryParent = 0" ;
      $PARENT_MENU_SQL_RESULT = mysql_query($PARENT_MENU_SQL_STATEMENT) or die("Query Failed - {$PARENT_MENU_SQL_STATEMENT}") ;

      $DEPTH = count($TRACER_ARRAY) ;
      
      while($PARENT_MENU_LINE = mysql_fetch_array($PARENT_MENU_SQL_RESULT))
      {
            echo "<li>" ;
            echo "<a href="index.php?GalleryID={$_LINE['GalleryID']}">{$_LINE['GalleryName']}</a>" ;
            $COUNTER = 0 ;
            if($TRACER_ARRAY[$DEPTH - $COUNTER] != 0)
            {
                  $SUB_MENU_SQL_STATEMENT = "SELECT * FROM family_galleries WHERE GalleryParent = {$TRACER_ARRAY[$DEPTH - $COUNTER]}" ;
            }

            echo "</li>" ;
      }

Unforetunately that is when I realised I need to write the code to work recursively itself, and that is when my brain broke... (ps the labels are different, because this is from a project I was first trying this on)... this recursiveness should only be on a single root menu item... thus the reason for the line "if($TRACER_ARRAY[$DEPTH - $COUNTER] != 0)" as then it would only go into that sub routine if it was a parent/grandparent of the current selected gallery.

Thanks
Zak
0
 
hernst42Commented:
If you use <li> tags its more easy:

$topElement = getRootOf($catid);
foreach ($rootElements as $element) {
  echo displayElement($element);
  if ($element == $topElement) {
     echo displayPath($catid, $level);
   }
}

function displayPath($id, &$level) {
    $Pid = getParentId($id);
    if ($Pid == 0) {
        // is already a root element
        return '';
    }
    return '<ul>' . displayPath($Pid, $level, $levels) . displayElement($id) . '</ul>';
}

function displayElement($id) {
   $result = '<li>';
   // get info of the database for that id
   $result .= $description;
   $result .= '</li>';
}

function getRootOf($id) {
    $COUNTER = 0;
    $TRACER_ARRAY[$COUNTER] = $id;
    while($TRACER_ARRAY[$COUNTER] != 0) {
        $TRACER_SQL_STATEMENT = "SELECT CatagoryParent FROM catagories WHERE CatagoryID = {$TRACER_ARRAY[$COUNTER]}" ;
        $TRACER_SQL_RESULT = mysql_query($TRACER_SQL_STATEMENT) ;
        $TRACER_SQL_LINE = mysql_fetch_array($TRACER_SQL_RESULT) ;
        $COUNTER++ ;
        $TRACER_ARRAY[$COUNTER] = $TRACER_SQL_LINE['CatagoryParent'] ;
    }
     return $TRACER_ARRAY[$COUNTER-1];
}

That code should work, but you need to implement the display-function (how an element of the tree should be shown) by your own. YOu also need to get a list of root-elements and the order to display an put those information into the array $rootElements.
0
 
Zak_MakeWithTheFunnyAuthor Commented:
Alright, I can kind of see how this is working (or supposed to) though I am still encountering some issues. Here is the modified functions:

      function displayMenuPath($id, &$level, $levels)
      {
            $levels++ ;
            $ParentID = getRootOf($id) ;
            if($ParentID == 0) { return '' ;}
            return "<ul>" . displayMenuPath($ParentID, $level, $levels) . displayMenuElement($id, $levels - $level) . "</ul>" ;
      }
      
      
      function displayMenuElement($id)
      {
            $scopedLink = mysql_connect(MOO MOO MOO) or die("Could not connect to server") ;
            mysql_select_db("MOOMOO") or die("Could not select database.") ;
            
            
            $scopedStatement = "SELECT * FROM furniture_gallery WHERE GalleryID = {$id}" ;
            $scopedResult = mysql_query($scopedStatement) or die("Query Failed - {$scopedStatement}") ;
            
            $scopedLine = mysql_fetch_array($scopedResult) ;
            
            $result = "<li><a href=\"index.php?GalleryID={$scopedLine['GalleryID']}\">{$scopedLine['GalleryName']}</a></li>" ;
            
            mysql_free_result($scopedResult) ;
            mysql_close($scopedLink) ;
            
            return $result ;
            
      }
      
      function getRootOf($id)
      {
            $scopedLink = mysql_connect(MOO MOO MOO) or die("Could not connect to server") ;
            mysql_select_db("MOOMOO") or die("Could not select database.") ;

            $COUNTER = 0;
            $TRACER_ARRAY[$COUNTER] = $id;
            while($TRACER_ARRAY[$COUNTER] != 0)
            {
                  $TRACER_SQL_STATEMENT = "SELECT ParentID FROM furniture_gallery WHERE GalleryID = {$TRACER_ARRAY[$COUNTER]}" ;
                  $TRACER_SQL_RESULT = mysql_query($TRACER_SQL_STATEMENT) ;
                  $TRACER_SQL_LINE = mysql_fetch_array($TRACER_SQL_RESULT) ;
                  $COUNTER++ ;
                  $TRACER_ARRAY[$COUNTER] = $TRACER_SQL_LINE['ParentID'] ;
            }
            mysql_free_result($TRACER_SQL_RESULT) ;
            mysql_close($scopedLink) ;
            return $TRACER_ARRAY; //[$COUNTER-1]; (Changed cause it was returning an in, not an array, and caused badness in the foreach statement)
      }

And what calls it:
      $PARENT_MENU_SQL_STATEMENT = "SELECT * FROM furniture_gallery WHERE ParentID = 0" ;
      $PARENT_MENU_SQL_RESULT = mysql_query($PARENT_MENU_SQL_STATEMENT) or die("Query Failed - {$PARENT_MENU_SQL_STATEMENT}") ;
      
      echo "<ul>" ;
      
      while($PARENT_MENU_LINE = mysql_fetch_array($PARENT_MENU_SQL_RESULT))
      {
            echo displayMenuElement($PARENT_MENU_LINE['GalleryID']) ;
            
            
            if(count($_GET['GalleryID']) != 0)
            {
                  if($PARENT_MENU_LINE['GalleryID'] == $_GET['GalleryID'])
                  {
                        $topElement = getRootOf($_GET['GalleryID']);
                        
                        foreach ($topElement as $element)
                        {
                              echo displayMenuElement($element);
                              if ($element == $topElement)
                              {
                                    echo displayMenuPath($_GET['GalleryID'], $level, $levels); //Added $levels, because otherwise the function was missin the extra parameter.
                              }
                        }
                        
                  }
            }
            
      }
      echo "</ul>" ;


Now, the issue that I am encountering is that if you select a subcatagory, it returns this: (this is the source from the html page)

<ul><li><a href="index.php?GalleryID=1">Tables</a></li>
<li><a href="index.php?GalleryID=4">Cabinets</a></li>
<li><a href="index.php?GalleryID=4">Cabinets</a></li>
<li><a href="index.php?GalleryID="></a></li></ul>

(Carriage returns added by yours truly to make it easier to read :D)

I have never used a foreach statement before, so I am not sure where the error is. For all intents and purposes, it looks like your code you wrote should work, (btw sorry if I sounded arrogant before, I re-read my post and realised I sounded like an arrogant nasty man)

I tried changing the if ($element == $topElement) to if ($element != $topElement) but that just started an infinite loop...

Any extra help would be greatly appreciated, and for my being an arrogant bugger, I'll add an extra 50 points on...
0
 
Zak_MakeWithTheFunnyAuthor Commented:
Eh... solved this one anyway... worked the menu a different way, and didn't need to do it this way... but I'll award the points to hernst42 anyways...

Zak
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now