Need help in creating dynamic menu

Question

I am developing a website using PHP and this is the 1st time I am trying a dynamic menu.

What I am doing is I have created a table for pages.

The table structure is as follows:

____________________________________________________________________________
|page_id|title|url|content|menu_title|show_on_navuigation|is_sub|parent_page|
|_______|_____|___|_______|__________|___________________|______|___________|

Here is_sub indicates whether it is a dropdown menu of some main menu. and parent_page is the main menu of the sub menu.

Now, what I want is, like

I have created 2 parent pages:

About Us and Test

and 2 sub menu's: Test aboutus and testsub,

Test aboutus is sub of About Us and testsub is sub of test.

How actually should the query and the loop, so that the menu renders perfectly.

Thanks in advance.

This is my menu structure:

<ul>
    <li class='has-sub'><a href='#'><span>About Us</span></a>
      <ul>
         <li><a href='corporateprofile'><span>Corporate Profile</span></a></li>
         <li class='last'><a href='visionandmission'><span>Vision &amp; Mission</span></a></li>
      </ul>
   </li>
   <li class='has-sub'><a href='#'><span>Business Services</span></a>
      <ul>
         <li><a href='recruitment'><span>Recruitment</span></a></li>
         <li><a href='training'><span>Training</span></a></li>
         <li><a href='executivesearch'><span>Executive Search</span></a></li>
         <li><a href='payroll'><span>Payroll</span></a></li>
         <li class='last'><a href='backgroundverification'><span>Background Verification</span></a></li>
      </ul>
   </li>
   <li class='has-sub'><a href='#'><span>Employers</span></a>
      <ul>
         <li><a href='enquiry'><span>Enquiry</span></a></li>
         <li><a href='jobdescription'><span>Job Description</span></a></li>
         <li><a href='employercontract'><span>Employer Contract</span></a></li>
         <li class='last'><a href='feedback'><span>Feedback</span></a></li>
      </ul>
   </li>
   <li class='has-sub'><a href='javascript:;'><span>Job Seeker</span></a>
      <ul>
         <li><a href='applyforjob'><span>Apply For Job/Register</span></a></li>
         <li><a href='careertips'><span>Career Tips</span></a></li>
         <li><a href='interview Questions'><span>Interview Questions</span></a></li>
         <li><a href='interviewprocess'><span>Interview Process</span></a></li>
         <li class='last'><a href='corporatedress'><span>Corporate Dress</span></a></li>
      </ul>
   </li>
   <li class='has-sub'><a href='#'><span>Franchise</span></a>
      <ul>
         <li class='last'><a href='#'><span>Franchise Enquiry Form</span></a></li>
      </ul>
   </li>
   <li class='last'><a href='#'><span>Contact us</span></a></li> 

</ul>

<?php
function displayMenu($parent_page_id) {
      $sql = "SELECT * FROM `pages` WHERE `parent_page` = '$parent_page_id'"; // sql
      $result = mysql_query($sql);
      if( mysql_num_rows($result) === 0 ) { // mysql_num_rows() is deprecated, but you are using mysql extension so that's why I show it here
         return true; // exit function
      } // else, continue to rest of function
      echo '<ul>';
      while($row = mysql_fetch_assoc($result)) { // deprecated mysql php function here for simplicity
         echo '<li><a href="' . $result['url'] . '">' . $result['menu_title'] . '</a>'; // no closing </li> yet
         displayMenu($row['page_id']); // this is the recursive part
         echo '</li>'; // close the <li> from before the recursion
      }
      echo '</ul>';
}
$get_base_menu = mysql_query("SELECT * FROM pages WHERE parent_page = 0");
while($fetch_base_menu = mysql_fetch_array($get_base_menu))
{
    $parent_page_id = $fetch_base_menu['page_id'];
    displayMenu($parent_page_id);

}

?>

Do you really want to do all this work with mysql for a menu? The menu is usually the same across the entire site and rarely changes. So it makes sense, I think, to just hard-code it in HTML. It only makes sense to put it in the database, I think, if you're going to be frequently changing it, AND if you are creating some kind of "backend" to give your end user a way to edit the menu structure. Otherwise, why use a database for this? — Buttle Butkus
– Buttle Butkus, Commented Mar 9, 2013 at 8:44
Yes Buttle. The client wants an option to add more pages in future. So I had to create a table for the pages and menu — Vishal
– Vishal, Commented Mar 9, 2013 at 8:46
@ButtleButkus that's what I was thinking of. Usually menu items are usually the same except for maybe changing something login/logout and register/login. — Class
– Class, Commented Mar 9, 2013 at 8:47
Vishal, so your client might want to create any number of levels in the menu, to access different pages. But how will they create the pages? I assume that if they are going to handle the coding of new pages, they can handle adding some HTML to the menu "widget". — Buttle Butkus
– Buttle Butkus, Commented Mar 9, 2013 at 8:49
Buttle, I have created a form in the backend to be able to create pages. — Vishal
– Vishal, Commented Mar 9, 2013 at 8:53

Kisuka · Accepted Answer · 2013-03-09 09:45:05Z

1

I highly suggest trying to get away from using an Adjacency List model and move toward a much easier to manage solution, such as a nested set. Using an MPTT type solution should help you manage your hierarchical data much easier. Using an Adjacency List model you are limited at a certain point.

I'd suggest looking into using something along the lines of Zebra_MPTT, or some other form of MPTT library. Please checkout this article on Managing Hierarchical data in MySQL.

answered Mar 9, 2013 at 9:45

Kisuka

1,8621 gold badge15 silver badges10 bronze badges

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

3 Comments

Buttle Butkus Over a year ago

It looks like we have consensus about that Hillyer link.

Kisuka Over a year ago

Indeed :) There was another article I read awhile back but I'm having some trouble finding it now.

Buttle Butkus Over a year ago

I had the above one in my bookmarks, but for some reason they removed it from mysql.com. Luckily, I was able to find it by googling the title saved in my google bookmarks.

Buttle Butkus · Accepted Answer · 2013-03-09 10:19:39Z

0

I think the simplest thing for you to code will be something like this recursive function. All you have to do is put it on your page and make sure you have parent_page_id = 0 for the base level menu items. (I'm using parent_page_id instead of parent_page because I assume that is what you mean and it is more clear for the answer.)

Calling the function with an argument of "0" should give you the full menu tree.

$trace = array();

function displayMenu($parent_page_id) {
  $sql = "SELECT * FROM `tbl_menu` WHERE `parent_page_id` = $parent_page_id"; // sql
  $result = mysql_query($sql);
  if( mysql_num_rows($result) === 0 ) { // mysql_num_rows() is deprecated, but you are using mysql extension so that's why I show it here
     $trace[] = "Page_id $parent_page_id has no children";
     return true; // exit function
  } // else, continue to rest of function
  echo '<ul>';
  while($row = mysql_fetch_assoc($result)) { // deprecated mysql php function here for simplicity
     $trace[] = "Entered while loop for page_id = $parent_page_id"; // not an error, just tracking
     echo '<li><a href="' . $row['url'] . '">' . $row['menu_title'] . '</a>'; // no closing </li> yet
     displayMenu($row['page_id']); // this is the recursive part
     echo '</li>'; // close the <li> from before the recursion
  }
  echo '</ul>';
}

displayMenu(0); // call function for base level

var_dump($trace);

More info:

I have researched the topic before and had a nice link to a page on mysql.com with an in-depth exploration of hierarchical relational database structures. But when I checked, the link was broken!

But, I think I found it on another site, here:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

edited Mar 9, 2013 at 10:19

answered Mar 9, 2013 at 9:43

Buttle Butkus

9,54713 gold badges86 silver badges126 bronze badges

10 Comments

Vishal Over a year ago

Buttle I am using mysql_query

Vishal Over a year ago

Buttle, I will try and get back to you if I have any queries. Thanks

Buttle Butkus Over a year ago

Ok. I changed the code to use the mysql functions and I put in the actual mysql query, except I use parent_page_id instead of parent_page because that is more clear to other people.

Vishal Over a year ago

Yes, ok Buttle. Wil try that

Vishal Over a year ago

Hi Buttle, I have posted the query i have written in my question. Its not showing any output. I can see only html tags in view source, but no text inside the tags. Is it correct what I have written. Could you please let me know the changes if any

|

Collectives™ on Stack Overflow

Need help in creating dynamic menu

2 Answers 2

3 Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related