hello..

i am creating cmc pages from admin side. i have 3 level menu in front end. my database table structure for cms pages 'name,parent_id,description,status'.

now i have to display all pages subpages and sub of subpages in menu.

how to write this query?

<!-- BEGIN Menu -->
     <ul id="nav">
 <li><a href="consumers.php">Consumers</a>
   <ul><li><a href="personal_finance.php">Personal Finance </a>
 <ul><li><a href="personal_financial_makeover.php">Personal Financial Makeover</a></li>
       <li><a href="personal_finance_consultancy.php">Personal Finance Consultancy</a></li>
      </ul>
     </li>
     <li><a href="investings.php">Investings</a>
      <ul>
  <li><a href="securities_analysis.php">Securities Analysis</a></li>
      <li><a href="portfolio_investment.php">Bernie Madoff Shield (Portfolio and Investment Analysis)</a></li>
     </ul>
     </li>
    </ul>
          </li>
         <li><a href="entrepreneurs.php">Entrepreneurs</a>
            <ul>
     <li><a href="market_analysis.php">Market Analysis and Competitive Report </a></li>
              <li><a href="business_plans_feasibility.php">Business Plans &amp; Feasibility Studies </a>
             <ul>
             <li><a href="business_plans.php">Business Plans</a>
                    <ul>
                       <li><a href="level1.php">Level 1</a></li>
                       <li><a href="level2.php">Level 2</a></li>
                       <li><a href="level3.php">Level 3</a></li>
                     </ul>

                   </li>
       </ul>

      </li>
                   </ul>

  <!-- END Menu -->

Ok , Please use this code!

By this code you can create unlimited menus!!!

At the first you should to create a db with this feature:

CREATE TABLE `menu_item` (
  `id` int(11) NOT NULL,
  `title` varchar(75) DEFAULT NULL,
  `link` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

And insert into this table sample data!

INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (1,'1','1.html',0,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (2,'2','2.html',0,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (3,'11','11.html',1,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (4,'12','12.html',1,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (5,'21','21.html',2,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (6,'22','22.html',2,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (7,'3','3.html',0,3);

And with this php class you can see the output menus via the <ul><li> tags!

<?php
class MenuBuilder
{
	var $conn;
	var $items = array();
	var $html  = array();
	function MenuBuilder()
	{
		$this->conn = mysql_connect( 'localhost', 'user', 'pass' );
		mysql_select_db( 'example', $this->conn );
	}
	function fetch_assoc_all( $sql )
	{
		$result = mysql_query( $sql, $this->conn );
		
		if ( !$result )
			return false;
		$assoc_all = array();
		while( $fetch = mysql_fetch_assoc( $result ) )
			$assoc_all[] = $fetch;
		mysql_free_result( $result );	
		return $assoc_all;
	}
	function get_menu_items()
	{
		$sql = 'SELECT id, parent_id, title, link, position FROM menu_item ORDER BY parent_id, position;';
		return $this->fetch_assoc_all( $sql );
	}
	function get_menu_html( $root_id = 0 )
	{
		$this->html  = array();
		$this->items = $this->get_menu_items();
		foreach ( $this->items as $item )
			$children[$item['parent_id']][] = $item;
				$loop = !empty( $children[$root_id] );
		$parent = $root_id;
		$parent_stack = array();
		$this->html[] = '<ul>';
		while ( $loop && ( ( $option = each( $children[$parent] ) ) || ( $parent > $root_id ) ) )
		{
			if ( $option === false )
			{
				$parent = array_pop( $parent_stack );
				$this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 ) . '</ul>';
				$this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ) . '</li>';
			}
			elseif ( !empty( $children[$option['value']['id']] ) )
			{
				$tab = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 );
				
				// HTML for menu item containing childrens (open)
				$this->html[] = sprintf(
					'%1$s<li><a href="%2$s">%3$s</a>',
					$tab,   // %1$s = tabulation
					$option['value']['link'],   // %2$s = link (URL)
					$option['value']['title']   // %3$s = title
				); 
				$this->html[] = $tab . "\t" . '<ul class="submenu">';
				
				array_push( $parent_stack, $option['value']['parent_id'] );
				$parent = $option['value']['id'];
			}
			else
				// HTML for menu item with no children (aka "leaf") 
				$this->html[] = sprintf(
					'%1$s<li><a href="%2$s">%3$s</a></li>',
					str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ),   // %1$s = tabulation
					$option['value']['link'],   // %2$s = link (URL)
					$option['value']['title']   // %3$s = title
				);
		}
		
		// HTML wrapper for the menu (close)
		$this->html[] = '</ul>';
		
		return implode( "\r\n", $this->html );
	}
}
?>

For using above class:

<?php
$menu = new MenuBuilder();
echo '<pre>' . htmlentities( $menu->get_menu_html() ) . '</pŕe>';
?>

Enjoy. ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.