creating menu structure from mysql database

Question

I'm just wondering on peoples opinions here, and perhaps some guidance on how best to implement something.

What I have, is a very basic intranet, simply designed to serve up company wide documents and files. It has a basic menu bar across the top of the page, that has a top level item, and then sub menus/items. Obviously the menu can cascade down many levels, and is a simple javascript I got of dynamic drive, that turns an html < li > structure into the menu.

Basic idea is this:

<li><a href='#'>Top Level 1</a>
    <ul>
        <li><a href='#'>Item 1</a></li>
        <li><a href='#'>Item 2</a></li>
        <li><a href='#'>Sub menu 1</a>
            <ul>
                <li><a href='#'>Sub Item 1</a></li>
            </ul>
        </li>
    </ul>
</li>

So this is used to create the menu. Every time you start a new < ul > element, it will create a sub menu.

This works fine, but is a bit of a pain as I have to manually go in an create new links, delete old ones etc. everytime there is a change in documents on the site.

What I am looking to do, is move this structure into my database, so that I can use basic html forms, and PHP scripts to insert, update and delete links when needed.

It also gives managers the ability to upload new files if I am not available to do so.

I have had a quick play with this, and have found an option that kind of works, but is a bit clunky and could be a pain to maintain going forward.

Basically, I would have a table with the columns listed below:

Doc ID

Filename

File path

Menu

Sub 1

Sub 2

Sub 3

Label

Open In

What I can do, is to have a unique ID for reference, input the filename and file path that together can be used for the href link to point to the files location. I can then use the menu and 3 sub menu fields to tell it where to place that item in the menu structure. The label just gives a name to the link, and the open in tells the page where to open the document, i.e. _new, frame, ifram etc.

The PHP can then use a mysql query to find all items in a menu and echo them out. The problem comes that I would need a seperate MySQL query for each menu and sub menu that I have, meaning that the entire menu may well end up being full of MySQL queries.

Can anyone advise on a better solution to this, or help make the PHP as simple and flexible as possible?

Many thanks

Eds

Edit:

Can provide some examples of PHP if required, but they are quite large.

Anton · Accepted Answer · 2012-04-24 15:05:39Z

2

best practise for this as i know is nested tree structure, which logic is in two words:

table required fields: id, parent_id, left, right

then, in adding first row, you got this values

1, 0, 1, 2

next will improve left and right fields like:

2, 0, 3, 4
3, 0, 5, 6

and if we need to make subdirectory for first item, the full structure will be this:

1, 0, 1, 4
2, 0, 5, 6
3, 0, 7, 8
4, 1, 2, 3

so you can easily take all the tree ordered by left, and then just check every row parent level value, if parrent exists, to calculate its own level.

answered Apr 24, 2012 at 15:05

Anton

1,0518 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anton Over a year ago

left and right are telling you the real structure of tree.. you can easily take parents of third level item, and same easy take all childrens of any item. so if we need all children and subchildren of 1 branch, taking where left>1 and right<4. if needed only children of 1 branch, take it by parent_id.

Drahcir Over a year ago

I found this article that helped me to understand left & right values: sitepoint.com/hierarchical-data-database-2

Jim Garbe · Accepted Answer · 2017-04-03 15:03:36Z

0

I developed this "Git" menu because I couldn't find anything MySQL driven either. https://github.com/jgarbe/DDrop_Menu I hope someone can use it!

answered Apr 3, 2017 at 15:03

Jim Garbe

93 bronze badges

Collectives™ on Stack Overflow

creating menu structure from mysql database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related