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
Zak_MakeWithTheFunnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.