how to create tree menu use data in mysql?

Question

I want to create dynamic menu tree using php and data in MYSQL.

I have table in mysql with the field:

CREATE TABLE IF NOT EXISTS sys_menu (
  menu_code varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  menu_name varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  menu_link varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (menu_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO sys_menu (menu_code, menu_name, menu_link) VALUES
('01', 'setting', NULL),
('01.01', 'admin', NULL),
('01.01.01', 'add', 'add.php'),
('01.01.02', 'edit', 'edit.php');

Eventually, the HTML should like the code below.

   <ul>
    <li><a href="">Setting</a>
      <ul>
      <li><a href="">Admin</a>
          <ul>
            <li><a href="add.php">Add</a></li>
            <li><a href="edit.php">Edit</a></li>
          </ul>
      </li>
      </ul>
    </li>
    </ul>

Can anyone help me solve this problem. Thanks in advance.

can you change table structure ? instead of 01.01.01, you can have menu_code, parent_menucode in your table ? — GBD
– GBD, Commented Sep 20, 2012 at 7:13
To manage the Tree/ Hierarchy the 'ParentId' Column (to store the Parent id of item) is best Practice ! — Bhavin Rana
– Bhavin Rana, Commented Sep 20, 2012 at 7:24

Nicole Stutz · Accepted Answer · 2012-10-06 15:18:25Z

3

Another solution, should be able to deal with various sizes of list levels.

$listData[]=array('menu_code' => '01','menu_name' => 'setting', 'menu_link' => null);
$listData[]=array('menu_code' => '01.01','menu_name' =>  'admin', 'menu_link' => null);
$listData[]=array('menu_code' => '01.01.01','menu_name' =>  'add', 'menu_link'   =>'add.php');
$listData[]=array('menu_code' => '01.01.02','menu_name' =>  'edit', 'menu_link' => 'edit.php');
$listData[]=array('menu_code' => '02','menu_name' =>  'people', 'menu_link' => null);
$listData[]=array('menu_code' => '02.01','menu_name' =>  'people_edit', 'menu_link' => 'edit.php');
$listData[]=array('menu_code' => '02','menu_name' => 'people', 'menu_link' => null);

now the function

function createListMenu($listData){
    $str ='<ul>';
    $lastListLevel=0;
    $firstRow=true;

    foreach($listData as $row){
        $currentListLevel=count(explode('.',$row['menu_code'])) -1 ; // minus one to correct level to Level 0
        $differenceLevel=$currentListLevel-$lastListLevel;

        // deal with the link
        $hrefValue = is_null($row['menu_link']) ? '' : $row['menu_link'];
        $rootLevel=false;

        if($differenceLevel==0){        // stay on the same list level
            if($firstRow){
                $firstRow=false;
            }else{
                $str .='</li>';
            }
            $str .='<li>';
        }elseif($differenceLevel>0){    // go deeper in list level
            for($j=0;$j<$differenceLevel;$j++){
                $str .='<ul><li>';
            }
        }elseif($differenceLevel<0){    // go higher in list level
            for($j=$differenceLevel;$j<=0;$j++){
               if($j==0){  // root level reached
                   $rootLevel=true;
                   $str .='</li>';
               }else{
                   $str .='</li></ul>';
               }
            }
            $str .= ($rootLevel) ? '<li>' : '<ul>';
        }
        $str.='<a href="'.$hrefValue.'">'.$row['menu_name'].'</a>';
        $lastListLevel=$currentListLevel;
    }

    // set end list tags
    for($j=$lastListLevel;$j>=0;$j--){
        $str .='</li></ul>';
    }
    return $str;
}

echo createListMenu($listData);

edited Oct 6, 2012 at 15:18

answered Sep 20, 2012 at 8:18

Nicole Stutz

5164 silver badges15 bronze badges

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

2 Comments

radhit663831 Over a year ago

Daniel Stutz: what different logic source code php menu tree and menu dropdown with equal table MySql?

Nicole Stutz Over a year ago

Sorry radhit, but I don't get your question. In fact you are using no verbs in your question :)

Mihai Iorga · Accepted Answer · 2012-09-20 08:09:15Z

1

The database design is completely wrong, you will have lots of issues doing that.

Instead you can change to a parent like design:

CREATE TABLE IF NOT EXISTS `sys_menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_name` varchar(64) NOT NULL,
  `menu_link` varchar(255) NOT NULL,
  `parent` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


INSERT INTO `sys_menu` (`id`, `menu_name`, `menu_link`, `parent`) VALUES
(1, 'setting', NULL, 0),
(2, 'admin', NULL, 1),
(3, 'add', 'add.php', 2),
(4, 'edit', 'edit.php', 2);

Then you can make that menu like:

$db = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

function checkChilds($rows, $id) {
    foreach ($rows as $row) {
        if ($row['parent'] == $id) {
            return true;
        }
    }
    return false;
}
function createMenu($rows, $parent=0){  
    $result = "<ul>";
    foreach ($rows as $row){
        if ($row['parent'] == $parent){
            if($row['menu_link']){
                $result .= "<li><a href='".$row['menu_link']."' />".$row['menu_name']."</a>\n";
            } else {
                $result .= "<li>".$row['menu_name']."\n";
            }
            if (checkChilds($rows, $row['id'])){
                $result.= createMenu($rows,$row['id']);
            }
            $result .= "</li>\n";
        }
    }
    $result .= "</ul>\n";

    return $result;
}

$stmt = $db->query("SELECT * FROM `sys_menu`");
$menu = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo createMenu($menu);

edited Sep 20, 2012 at 8:09

answered Sep 20, 2012 at 7:48

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

3 Comments

radhit663831 Over a year ago

thank Mihai Iorga but if use parent ID structure field is not beautiful.

Mihai Iorga Over a year ago

Beautiful? It's a good design with parent id. also is easier to manipulate. How would you get the parent from last child? Doing 3 queries to explode by that number??

Phil Cross Over a year ago

Thank you very much for this answer @MihaiIorga I've had my database set up like in the example above, but my code was inefficient. This looks like exactly what I was looking for! +1

Afshin · Accepted Answer · 2012-09-20 08:19:22Z

<?php
    $connection=mysql_connect(DB_Server,DB_User,User_Pass);
            $db=mysql_select_db(DB_Name);
    $sql="select * from sys_menu order by menu_code";
    $res=mysql_query($sql) or die(mysql_error());
    echo '<ul>';
    while($row=mysql_fetch_array($res))
        {
            $cur=$row['menu_code'];
            if (strlen($cur)==2)
            {
                echo '<li><a href="">'.$row['menu_name'].'</a><li>';

                $res2=mysql_query($sql) or die(mysql_error());
                echo '<ul>';
                $mainpar=$cur;
                while($row=mysql_fetch_array($res2))
                    {
                        $cur=$row['menu_code'];
                        if ((strlen($cur)==5)&&(substr($cur,0,2)==$mainpar))
                        {
                            echo '<li><a href="">'.$row['menu_name'].'</a><li>';
                            $res3=mysql_query($sql) or die(mysql_error());
                            echo '<ul>';
                            $secpar=$cur;
                            while($row=mysql_fetch_array($res3))
                                {
                                    $cur=$row['menu_code'];
                                    if ((strlen($cur)==8)&&(substr($cur,0,5)==$secpar))
                                    {
                                        echo '<li><a href="'.$row['menu_link'].'">'.$row['menu_name'].'</a><li>';
                                    }   
                                 }
                                 echo '</ul>';
                        }
                    }
                    echo '</ul>';
            }
        }
        echo '</ul>';
?>

Collectives™ on Stack Overflow

how to create tree menu use data in mysql?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related