Mysql/php tree navigation menu

Question

Code output needed:

Visual Output :

My sql Database 'test'

CREATE TABLE IF NOT EXISTS `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `link` varchar(100) NOT NULL,
  `parrent_id` int(11) NOT NULL DEFAULT '0',
  `status` varchar(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `menu`
--

INSERT INTO `menu` (`id`, `name`, `link`, `parrent_id`, `status`) VALUES
(1, 'Home', '#home', 0, 'Show'),
(2, 'About', '#about', 0, 'Show'),
(3, 'Our services', '#service', 0, 'Show'),
(4, 'Get involved', '#getinvolved', 0, 'Show'),
(5, 'Donors', '#donors', 0, 'Show'),
(6, 'News', '#news', 0, 'Show'),
(7, 'Contact Us', '#contact', 0, 'Show'),
(8, 'Ourselves', '#ourselves', 2, 'Show'),
(9, 'Objectives', '#objectives', 2, 'Show'),
(10, 'Our Patrons', '#patrons', 2, 'Show');

My PHP CODE is Here....

<?php
  $dbuser="root";
  $dbpass="";
  $dbname="test";  //the name of the database
  $chandle = mysql_connect("localhost", $dbuser, $dbpass)
    or die("Connection Failure to Database");
  mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);

  function menu($parrent) {
    echo "<ul>";
    $sql = "SELECT * from menu where parrent_id=".$parrent;
    while($res = mysql_fetch_array(mysql_query($sql)))
    echo "<li><a href='".$res['link']."'>".$res['name']."</a></li>";
    echo "</ul>";
  }
  $sql = "SELECT * from menu where parrent_id =0";
  echo "<ul>";
  while($resnew = mysql_fetch_array(mysql_query($sql)))
  {
    $sql_sub = "SELECT * from menu where parrent_id=".$resnew['id']; 
    $count = mysql_num_rows(mysql_query($sql_sub));
    if($count>0)
    {
      echo "<li><a href='".$resnew['link']."'>".$resnew['name']."</a>";   
      menu($resnew['id']);
      echo "</li>";
    }
    else
    {
      echo "<li><a href='".$resnew['link']."'>".$resnew['name']."</a></li>";
    }
  }
  echo "</ul>";
?>

Answer 1

Menu Tree

a. Create the table "categorylist".

CREATE TABLE IF NOT EXISTS `categorylist` (
`id` int(5) NOT NULL auto_increment,
`cname` varchar(25) collate utf8_unicode_ci default NULL,
`pid` int(5) NOT NULL,
`url` text collate utf8_unicode_ci,
`status` int(1),
PRIMARY KEY (`id`),
KEY `pid` (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

---

b. Insert values in the "categorylist"

INSERT INTO `categorylist` (`id`, `cname`, `pid`, `url`, `status`) VALUES
(1, 'Entertainment', 0, '#',1),
(2, 'Movies', 1, 'http://example.com/movie.php',1),
(3, 'Drama', 1, 'http://example.com/drama.php',1),
(4, 'Sports', 0, '#',1),
(5, 'Football', 4, 'http://example.com/football.php',1),
(6, 'Cricket', 4, 'http://example.com/cricket.php',1),
(7, 'Politics', 0, '#',1),
(8, 'Politics news', 7, 'http://example.com/politics-news.php',1);

---

c. Style sheet

.nav ul li { background:#f1f1f1; list-style: none;}
ul.dropdown { position:relative; width:auto; font:12px Arial, Helvetica, sans-serif; }
ul.dropdown li { float:left; zoom:1; height:30px; padding:6px 2px 0 2px; }
ul.dropdown li li { border-right:1px solid #ccc; border-left:1px solid #ccc; margin-left:-30px;}
ul.dropdown a:hover { color:#000; } ul.dropdown a:active { color:#ffa500; }
ul.dropdown li a { display:block; padding:4px 8px; color:#000; text-decoration:none; font:bold 12px Arial, Helvetica, sans-serif; }
ul.dropdown li:last-child a { border-right:none;} /* Doesn't work in IE */
ul.dropdown li:hover { color:#000; background:#e7e7e7; position:relative; }
ul.dropdown li.hover a { color:#000; }
ul.dropdown ul { text-align:left; visibility: hidden; position: absolute; left:-10px; top:36px; }
ul.dropdown ul li { background:#f1f1f1; border-bottom:1px solid #ccc; float:none; width:120px; height:25px; }
ul.dropdown ul li a { border-right:none; width:100%; display:inline-block; color:#000; }
ul.dropdown ul ul { left:100%; top:0; }
ul.dropdown li:hover > ul { visibility:visible; }

---

d. Code for menu script

<?php

$con=mysql_connect("localhost", "root", "") or die('Server connexion not possible.');
mysql_select_db("newone",$con) or die('Database connexion not possible.');

$qry="SELECT * FROM categorylist" where status=1;
$result=mysql_query($qry,$con);


$arrayMenu = array();

while($row = mysql_fetch_assoc($result)){
$arrayMenu[$row['id']] = array("pid" => $row['pid'], "name" => $row['cname'], "url" => $row['url']);
}


//createTree($arrayCategories, 0);

function createTree($array, $curParent, $currLevel = 0, $prevLevel = -1) {

foreach ($array as $categoryId => $category) {

if ($curParent == $category['pid']) {

if($category['pid']==0) $class="dropdown"; else $class="sub_menu";
if ($currLevel > $prevLevel) echo " <ul class='$class'> ";


if ($currLevel == $prevLevel) echo " </li> ";

echo '<li id="'.$categoryId.'" >&lt;a href="'.$category['url'].'"&gt;'.$category['name'].'&lt;/a&gt;';

if ($currLevel > $prevLevel) { $prevLevel = $currLevel; }

$currLevel++;

createTree ($array, $categoryId, $currLevel, $prevLevel);

$currLevel--;
}
</pre>
}

if ($currLevel == $prevLevel) echo " </li> </ul> ";

}
?>

<div class="nav">

<?php
if(mysql_num_rows($result)!=0)
{
php createTree($arrayMenu, 0);
}
?>
</div>

Answer 2

This I yust coded with ORM in Kohana Framework. Perhaps it helps. If not, feel free to be inspired...

public function roleTree(){
    // find topmost nodes
    $roles = ORM::factory('Role')->where('parent_id', '=', '0')->find_all();
    $nodes = array('node' => NULL, 'depth' => 0, 'childs' => array());
    foreach($roles as $role){
        $nodes['childs'][] = array('node' => $role, 'depth' => 1, 'childs' => $this->_roleTree($role, 1));
    }
    return $nodes;
}

private function _roleTree($role, $depth){
    // add child nodes
    $depth ++;
    $roles = ORM::factory('Role')->where('parent_id', '=', $role->id)->find_all();
    $nodes = array();
    if(count($roles)){
        foreach($roles as $role){
            $nodes[] = array('node' => $role, 'depth' => $depth, 'childs' => $this->_roleTree($role, $depth));
        }
        return $nodes;
    }
    return array();
}

It creates a fancy structured but simple array...

Answer 3

Try this query this is called semi join

select
  l.id,
  l.name,
  group_concat(r.name)
from menu l
inner join menu r
on r.parrent_id = l.id
group by l.name

You can also provide a where condition to get the results according to your requirements here it will bring all the results if parent ids are not 0. How to use Group Concat Value use php explode function for this to work.