Creating a menu from a categories table

I've been having a go at making a menu of categories from my categories table, and have seen some examples online (that I didn't really like or didn't work), and was wondering if anyone has a great way to do it.

CREATE TABLE `product_categories` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(24) NOT NULL,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY (`category_name`)

I'd like to be able to make the menu with nested unordered lists, based on the category relationships as parent/child. One of my goals is to allow the categories to have unlimited depth, and I've spent quite a bit of time trying to work something up, just to scrap it.

If anyone has some sample code, a link to a good tutorial, or would like to contribute (because this is going in my Community Cart application), I'd be very thankful.

First of all you need to set up a sql query that fetches al the links, names, parent_id, etc you need. Decide if you wanna array or an object returned.
//in the controller, might be a MY_Controller so you'll be able to use it throughout the site.
$menu = $this->menu_model->get_menu();

If you need to alter $menu you could tinker with it in the controller. Now you have to decide whether you want to render the menu in the view or in a library. I often go with a library('html_render').
$this->data['menu_html'] = $this->html_render->make_menu($menu);
(Feels like a nice way of doing a thing I haven't figure out how to do in a strict mvc way.)

Feel free to ask questions as you go along.

Well, this isn't what I'm after. My problem is, that the database would return data that shows:

category_id category_name parent_id
1 Examples 0
2 Stuff 0
3 Widgets 1
4 Shiny 3

So, you can see that some parent Ids are 0, which means top level, and what I'm having a problem with is sorting and creating the menu. In this case the desired menu would look like this:

    <li><a href="examples">Examples</a>
            <li><a href="widgets">Widgets</a>
                    <li><a href="shiny">Shiny</a></li>
    <li><a href="stuff">Stuff</a></li>

I'm having a heck of a time sorting the categories and creating the menu, whether it be in a view, controller, or regardless of location, I just can't get it done.

Having infinite levels makes it much trickier .. are you sure you don't just want two levels? Way easier. {much nodding} I suspect recursion is your friend, obviously with some catches for when you get a loop.

What didn't you like about the examples you've found already on the net?

Slightly off topic, maybe.

A day ago, or something, I wrote that I don't like to do spit out sql querys. But when I read this thread I thought, but what if I cache the result in a file. Then I'd be able to rewrite the file only when there's no db hazard.

You need to set up proper html-tags if you try this approach/code. I'm not familiar enough with CI to scrabble it down in CI style. :red:
function makeMenu($parent = 0)
   $getLinks = mysql_query("/* query the menu stuff you need WHERE parent = $parent */");
   if (mysql_num_rows($getLinks) > 0)
      $list = '';
      while ($row = mysql_fetch_assoc($getLinks))
           $list .= makeMenu($row['id']);
           $list .= "<br />"; //Just to see if it works.

   return $list; // Cache this in a file!
Please tell me how it works... if it does. 8-/

[quote author="jedd" date="1259893656"]Having infinite levels makes it much trickier .. are you sure you don't just want two levels? Way easier. {much nodding} I suspect recursion is your friend, obviously with some catches for when you get a loop.

What didn't you like about the examples you've found already on the net?[/quote]

Well, I think the problem is that these examples don't go for infinite levels. I'd even be willing to settle for 3 or 4 levels, but 2 is a big limitation. 2 levels; Easier yes, but super cool, no.

My goal is to work on this until I have a stroke (if that's what it takes). I've been thinking about some solution with php dom or xml or something markup related. So if nobody else has any suggestions, then I guess that's what I try next.

If you feel like helping, just know that this will go into Community Cart, and that you will be given credit. Other than that, I'm not offering anything. Don't make me beg!


did that, parent -> child
btw- you can also sort the order of the parent and child by adding another field also a field for adding the link to the child page.
for the parent = #
for child = controller name

category_id category_name parent_id sort link
1 Examples 0 1 #
2 Stuff 0 2 #
3 Widgets 1 1 cont_name
4 Shiny 3 1 cont_name2
5 Nothing 1 2 cont_name3
so on.....

if (isset($navs)) {
    foreach ($navs as $parent)
        $parent_id = "nav_" . $parent['row']['category_id'];
                 echo "link" ....
                   foreach ($parent['child'] as $module)
            $module_id = "nav_" . $module['row']['category_id'];
            $module_link = "#";
            if ($module['row']['link']!="#") {
                $module_link = $module['row']['link'];
                        echo "link"
Just pointed of what ive done. Hope this help..

I'm sure there are nicely documented patterns out there to do this ...

Without going hunting, I speculate you need to either tune your input data - order on parent, id - for example .. and/or you do a couple of passes of the data, making a big n-dimensional array on the first pass, which you may do an intermediary pass on to (recursively) sort, before you do the relatively straightforward rendering bit.

I looked at something similar a while back, but opted to pull a single element, all its ancestors (unlike your data I had a single root, which I think makes it easier) and its immediate progeny. Rendering a potentially infinite (or even just 'big') set of data just gets messy. Sure, you can put limits on .. but I reason it would be better to do this earlier in that process than at render time.

I actually found a tutorial that should be exactly what I need. I just have to convert my query into a sorted array, and I already most of that work 2 nights ago. Need to modify the array a bit, but should have this done tonight (unless wife has other plans for me)

Check this out if curious:

For anyone trying to follow that link - manually change the '#' to '% 23' (no space).

By the way - will your data have sub-menus that contain both leaf items as well as sub-menus themselves, or will it always be a collection of one or the other only?

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.