0

I searched for awhile, but couldn't find anything on google and other forums. But it's weird because it's popular task in our university. So I think this post might help other too with same issue.

I got a task:

"Create multidimensional tree menu (depth is undefined, could be 4 or 7, depending on user's will), there should be options like adding new element, editing, deleting and showing the whole tree structure. Using PHP, SQL and minimal CSS."

Visual example of a tree:

+Menu
    +Shop
        +Tshirts
            +Yellow
            +Green
        +Pants
    +Forum
        +Ideas

As you can see it's 4 depth levels: Menu->Shop->tshirt->yellow But I have to make it so that user could add as many levels as he wants and elements to it.

Is there any examples to it and what SQL structure should I keep on? Thank you!

Improve this question
1

4 Answers 4

Reset to default
7

You want to save each element in the DB with an ID and a parentID (that can be null if no such parent exists). The PHP is your "biggest" problem, but references are your huge friend here for turning a flat structure into a tree structure.

Consider the following DB result:

----------------------------
| id | parentID | text     |
|----|----------|----------|
| 1  | null     | Item #1  |
| 2  | 5        | Item #2  |
| 3  | 2        | Item #3  |
| 4  | 2        | Item #4  |
| 5  | null     | Item #5  |
| 6  | 5        | Item #6  |
| 7  | 3        | Item #7  |
| 8  | 5        | Item #8  |
| 9  | 1        | Item #9  |
| 10 | 7        | Item #10 |
----------------------------

Consider the following array (that could be from a DB result - it's important that the ID is the key, though. You can simply transform your DB result into something like the following (the only needed key is "parentID"):

$menu = array(
    1 => array('text' => 'Item #1', 'parentID' => null),
    2 => array('text' => 'Item #2', 'parentID' => 5),
    3 => array('text' => 'Item #3', 'parentID' => 2),
    4 => array('text' => 'Item #4', 'parentID' => 2),
    5 => array('text' => 'Item #5', 'parentID' => null),
    6 => array('text' => 'Item #6', 'parentID' => 5),
    7 => array('text' => 'Item #7', 'parentID' => 3),
    8 => array('text' => 'Item #8', 'parentID' => 5),
    9 => array('text' => 'Item #9', 'parentID' => 1),
   10 => array('text' => 'Item #10', 'parentID' => 7),
);

And to turn it into a tree structure:

<?php    
$addedAsChildren = array();

foreach ($menu as $id => &$menuItem) { // note that we use a reference so we don't duplicate the array
    if (!empty($menuItem['parentID'])) {
        $addedAsChildren[] = $id; // it should be removed from root, but we'll do that later

        if (!isset($menu[$menuItem['parentID']]['children'])) {
            $menu[$menuItem['parentID']]['children'] = array($id => &$menuItem); // & means we use the REFERENCE
        } else {
            $menu[$menuItem['parentID']]['children'][$id] = &$menuItem; // & means we use the REFERENCE
        }
    }

    unset($menuItem['parentID']); // we don't need parentID any more
}

unset($menuItem); // unset the reference

foreach ($addedAsChildren as $itemID) {
    unset($menu[$itemID]); // remove it from root so it's only in the ['children'] subarray
}

With this new array we can use a simply recursive function to output it all in a ul..li sense:

echo makeTree($menu);

function makeTree($menu) {
    $tree = '<ul>';

    foreach ($menu as $id => $menuItem) {
        $tree .= '<li>' . $menuItem['text'];

        if (!empty($menuItem['children'])) {
            $tree .= makeTree($menuItem['children']);
        }

        $tree .= '</li>';
    }

    return $tree . '</ul>';
}

Resulting in:

<ul><li>Item #1<ul><li>Item #9</li></ul></li><li>Item #5<ul><li>Item #2<ul><li>Item #3<ul><li>Item #7<ul><li>Item #10</li></ul></li></ul></li><li>Item #4</li></ul></li><li>Item #6</li><li>Item #8</li></ul></li></ul>

..and rendered:

enter image description here enter image description here

DEMO

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

12 Comments

So how many tables I need for this in my database? How does it know which is the parent and which is the child? Could you put some comments in the code? I'm a little bit confused here. Thank you!
@Ignas Just one table with a row per menu item. "id" being the ID of the item and "parentID" being the ID of the parent menu item.
IKt's working, but how it knows who are whose children? Can you explain this line: $meniu[$meniuItem['parentID']]['children'] - where did you get that ['children']?
@Ignas $menu[$menuItem['parentID']] will be the parent $menuItem (if parentID is 5, this is equal to $menu[5]). Here we're first checking "does it have a children key?" If it doesn't, we'll create it, but if it already has a children key, then we simply add items to that array.
Oh I see, and later after we decide who is parent we unset it right?
|
0

I would suggest the following structure for the MySQL:

I_ID (unique, auto-increment), Item Name, Item Parent ID, Children

The "Children" would probably contain a base64-encoded, serialized PHP array of the I_IDs of each 'child' of that specific item.

The root element would have a "parent ID" of "root" or "-1" or something, so to retrieve this out of the database you'd first SELECT anything that had that parent of root or -1 or whatever you choose. It's arbitrary.

Then, with the PHP, you would decode the children array, and SELECT each of those IDs from the system.

Repeat until there are no more children. Recursive functions will serve you well here.

When you add each row into the DOM, give them a specific class that makes them hidden. When the "+" is clicked, use javascript to change all of that element's children to have a class that is not hidden, and replaces the just-clicked "+" button with a "-" button. Handle the minus button similarly.

Improve this answer

Comments

0

The solution is quite simple:

SQL structure table menu_entries (int id, varchar title, varchar link, int level, bool is_parent)

You can add options like additional links, colors and etc.

default level is 1. default is_parent = false;

After that proceed to get your entries into the database. If item has subitems - is_parent = true, and all subitems have level +1 of parent's level.

In outputting just add formating that any new "level" items are indented. Also, if item's link is current URL of the page - highlight the item as active.

That's all.

Improve this answer

Comments

0

Here's code from my link directory script's category table which does what you're looking for:

DB Design:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

To print them out using PHP in dashes:

function parse($tree,$parent = 0,$level = 0){
    $level++;
    foreach ($tree as $p)  {
        if ($p['parent'] != $parent) continue;
        $p['name'] = str_repeat('-',$level-1) . $p['name'];
        echo "<option name='cat' value='{$p['id']}'>{$p['name']}</option>";
        parse ($tree, $p['id'],$level);
    }
}

    $q = $link->query("SELECT * FROM `categories` ORDER BY name ASC");
    $cats = array();
    while($row = $q->fetch()){
        $cats[] = array("id"=>$row['id'],"name"=>$row['name'],"parent"=>$row['parent'],"level"=>0);
    }

parse($cats)

You can remove the $level param if you use

  • instead of number of dashes for output.

    • Improve this answer

    Comments

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

    Start asking to get answers

    Find the answer to your question by asking.

    Ask question

    Explore related questions

    See similar questions with these tags.