MySQL & PHP navigation menu

[metalhead]

I thought it would be best to have my navigational menu in MySQL, but I'm having problems with how best to extract the data. This is my table.

CREATE TABLE menu (
ID int(3) NOT NULL auto_increment,
parent int(3) NOT NULL default '0',
item varchar(40) NOT NULL default '',
link text,
user_only char(1) NOT NULL default 'N',
PRIMARY KEY (ID)
) TYPE=MyISAM;

Below is the data in my example table. Its pretty self-explanitory, column 2 (parent) defines what sub/menu the menu items are in. column 4 (link) is NULL if the menu item is a sub-heading and has no link, eg. sub-heading 'Fruit' has no link, but sub-heading 'Cars' does have a link. A 'Y' in column 5 (user_only) defines that the particular menu item should only be displayed if the user is logged on.

[code]

| 1 | 0 | Home Page | /index.php | N |
| 2 | 0 | Fruit | NULL | N |
| 3 | 2 | Apples | /apples.php | N |
| 4 | 2 | Oranges | /orangs.php | N |
| 5 | 0 | Vehicles | NULL | Y |
| 6 | 5 | Cars | cars.php | Y |
| 7 | 6 | Honda | honda.php | Y |
| 8 | 6 | Ford | ford.php | Y |
| 9 | 5 | Motorbikes | NULL | Y |
| 10 | 9 | Kawasaki | kawasaki.php | Y |
| 11 | 9 | Harley-Davidson | harley.php | Y |

| 12 | 0 | Contact us | contact.php | N |

[/code]

So, based on the data in my example table, this is what I want my menu to look like if the user is logged on. If however the user is not logged on then all menu items inside submenu 'Vehicles' (including submenus 'Cars' & 'Motorbikes') would be hidden.

Home Page
Fruit
 Apples
 Oranges
Vehicles
 Cars
  Honda
  Ford
 Motorbikes
  Kawasaki
  Harley-Davidson
Contact us

I plan on displaying the menu in a table, so before I start dynamically building it I need to know how many levels deep the submenus go in order to know what columns I need to span etc.

Does anybody know what MySQL queries and PHP code I should be using to do this?

Any help would be great as this is starting to do my head in now.

[drawmack]

I do db driven menus all the time. Here are some pointers

1) you're missing a field

dipOrder INT NOT NULL DEFAULT 0

2) here is a loop that i use, well I'm typing it into the message board so it's probably not 100% but it's close

$query = mysql_query("SELECT * FROM menu WHERE parent = 0 ORDER BY dispOrder");
for($i=0;$i<mysql_num_rows($query);$i++) {
  //see if we're working with a link here
  if(mysql_result($query,$i,3) != "") {
     echo "<a href=\"" . mysql_result($query,$i,3) . "\">" . mysql_result($query,$i,2) . "</a><br />\n";
  } else {
     echo mysql_result($query,$i,2) . "<br />\n";
  } //end if

  // create the submenu
  $query2 = mysql_query("SELECT * FROM menu WHERE parent =    echo mysql_result($query,$i,1) ORDER BY dispOrder");
  for($j=0;$j<mysql_num_rows($query);$j++) {
    //see if we're working with a link here
    if(mysql_result($query,$i,3) != "") {
       echo "&nbsp;&nbsp;<a href=\"" . mysql_result($query,$i,3) . "\">" . mysql_result($query,$i,2) . "</a><br />\n";
    } else {
       echo "&nbsp;&nbsp;" . mysql_result($query,$i,2) . "<br />\n";
    } //end if
  } //end for
} //end for

that will handle one level of sub menus. If you want something more dynamic look at recursion.