Database driven php menu system

[-Ricardo]

Hello,

 

I am making a small CMS and I having some trouble with making a dynamic menu. I am wondering for a few days how I should make this menu system. I have the follow MySQL table:

 

CREATE TABLE `pages` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(10) DEFAULT NULL,
  `lastby` varchar(2) DEFAULT NULL,
  `order_id` varchar(2) DEFAULT NULL,
  `text` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

Now I would like to have a menu system as following that I make menu's in a menu table with a name and a menu ID and that I could place those pages as parent or as subparent.

 

How could I manage to turn my theory into reality?

[creata.physics]

You'll need another field in your table to determine what is a parent and what is not, of course only if you decided to have sub links, sub-sub links and such.

 

For a 2 tier level of menus, you can simply perform a small query asking for all results

<?php 
$query = mysql_query("select id, parent, text from pages order by order_id asc");
while( $row = mysql_fetch_assoc( $query ) )
{
    if( $row['parent'] == 0 )
   {
       // This shows all main links
       echo $row['text'] . '<br/>';
   }
   else
   {
      // these are sub-links
      echo '--' . $row['text'] . '<br/>';
   }
}

That will display the main menu item with any children underneath.

 

Say you have one row, and it's a main link and it's ID is 1.  Well say you want a sub link showing under that main link, you will set the parent to match the id of the link you want it to display under. Also this will only display one set of children, if you want to make it deeper you'll need to alter the current code.

 

[-Ricardo]

Thank you for this theory and example code. How could I manage to have a table 'menu_setup' and there I control the names of the menu and if thats a URL or just a <li> to make a sub <li> and an external link instead of the pages ID?

 

<ul>
  <li><a href="/index.php?id=1">Home</a></li> <!-- Is an actual link -->
  <li>About</li>                              <!-- Is an placeholder for the submenu -->
  <ul>
    <li><a href="/index.php?id=2">Who are</a></li>  <!-- Submenu link -->
    <li><a href="/index.php?id=12">History</a></li> <!-- Submenu link -->
  </ul>
  <li><a href="/index.php?id=56">Rental</a></li>    <!-- Is an actual link -->
</ul>

 

I seem not to understand how my 'theory' is simple but google does return a few results based on asp. I just want some placeholder names where I have submenus.

[TimeBomb]

This is more of a database design problem than a PHP problem, but I would propose that you add one more field to your menu table. parentId

If parentId is null, then that row is not nested.

If parentId is not null, then that row is nested under the row wherein the id matches this parentId.

This allows for simple, dynamic, and infinitely-nested menus.

 

In terms of actual HTML implementation - well, it really depends how that menu is laid out. There are many different, valid options.

[-Ricardo]

That theory made sence all the way. Thank you for that TimeBomb. As for my imaganation I tried to make these tables and try to understand your theory.

 

The following is the menu table:

 

CREATE TABLE `pages` (
  `parentId` varchar(10) DEFAULT NULL,
  `parentName` varchar(230) DEFAULT NULL,
  `isMenu` varchar(2) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

And than I need to make the php code but I do not know where to start and how to link the table `menu` above to the table `pages` below:

 

CREATE TABLE `pages` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(10) DEFAULT NULL,
  `lastby` varchar(2) DEFAULT NULL,
  `order_id` varchar(2) DEFAULT NULL,
  `text` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

[TimeBomb]

That theory made sence all the way. Thank you for that TimeBomb. As for my imaganation I tried to make these tables and try to understand your theory.

 

The following is the menu table:

 

CREATE TABLE `pages` (
  `parentId` varchar(10) DEFAULT NULL,
  `parentName` varchar(230) DEFAULT NULL,
  `isMenu` varchar(2) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

And than I need to make the php code but I do not know where to start and how to link the table `menu` above to the table `pages` below:

 

CREATE TABLE `pages` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(10) DEFAULT NULL,
  `lastby` varchar(2) DEFAULT NULL,
  `order_id` varchar(2) DEFAULT NULL,
  `text` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

That was a typo in the first table code, I imagine? Supposed to be CREATE TABLE 'menu', not CREATE TABLE 'pages'.

Anyways, the menu table needs to have an id and parentId, not just a parentId. And why is there an isMenu field? What's that for?

Also, what is the pages table for? What do you define as a page in your application, and why does it need to be stored in the database, rather than some sort of 'view' PHP/HTML file.

[-Ricardo]

That was a typo in the first table code, I imagine? Supposed to be CREATE TABLE 'menu', not CREATE TABLE 'pages'.

Yes I am sorry. That was a typo. It had to be menu indeed.

 

Anyways, the menu table needs to have an id and parentId, not just a parentId. And why is there an isMenu field? What's that for?

As I typed a few posts back, I would like to have a menu system like this:

 

<ul>
  <li><a href="/index.php?id=1">Home</a></li>         <!-- Is an actual link -->
  <li>About</li>                                      <!-- Is an placeholder for the submenu -->
  <ul>
    <li><a href="/index.php?id=2">Who are</a></li>    <!-- Submenu link -->
    <li><a href="/index.php?id=12">History</a></li>   <!-- Submenu link -->
  </ul>
  <li><a href="/index.php?id=56">Rental</a></li>      <!-- Is an actual link -->
</ul>

 

As ifMenu is 1 then its a URL and if its a 0 its a placeholder like About and there is a submenu below it.

 

Also, what is the pages table for? What do you define as a page in your application, and why does it need to be stored in the database, rather than some sort of 'view' PHP/HTML file.

 

Because I have a lot of pages and I wanted to create them within a database table so I can edit them later on easy within a administration area, witch I am building later on when I am finished with this menu issue that is driving me nuts right now.

[TimeBomb]

That was a typo in the first table code, I imagine? Supposed to be CREATE TABLE 'menu', not CREATE TABLE 'pages'.

Yes I am sorry. That was a typo. It had to be menu indeed.

 

Anyways, the menu table needs to have an id and parentId, not just a parentId. And why is there an isMenu field? What's that for?

As I typed a few posts back, I would like to have a menu system like this:

 

<ul>
  <li><a href="/index.php?id=1">Home</a></li>         <!-- Is an actual link -->
  <li>About</li>                                      <!-- Is an placeholder for the submenu -->
  <ul>
    <li><a href="/index.php?id=2">Who are</a></li>    <!-- Submenu link -->
    <li><a href="/index.php?id=12">History</a></li>   <!-- Submenu link -->
  </ul>
  <li><a href="/index.php?id=56">Rental</a></li>      <!-- Is an actual link -->
</ul>

 

As ifMenu is 1 then its a URL and if its a 0 its a placeholder like About and there is a submenu below it.

 

Also, what is the pages table for? What do you define as a page in your application, and why does it need to be stored in the database, rather than some sort of 'view' PHP/HTML file.

 

Because I have a lot of pages and I wanted to create them within a database table so I can edit them later on easy within a administration area, witch I am building later on when I am finished with this menu issue that is driving me nuts right now.

 

If menu items need to link to pages, then you should add a separate column to the menus table: pageId. Wherein the menu table's pageId links up with the page table's id.

If pageId is null, then the menu item is a placeholder and not a link. This will remove the need for the isMenu field.

 

Storing pages (which contain front-end content) in the database is poor design, but that's a different subject.