Recursive PHP/MySQL list menu

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
deemjay
Forum Newbie
Posts: 4
Joined: Fri Dec 12, 2008 4:13 pm

Recursive PHP/MySQL list menu

Post by deemjay »

Can someone solve my issue please?

I want to produce this:-

<ul>
<li>Home</li>
<li>About</li>
<ul>
<li>About sub menu</li>
<ul>
<li>Under that</li>
etc etc etc
</ul>
</ul>
</ul>

A limitless recursive menu basically.

I have my DB set up =>[ id,name,parent_id ] with parent_id linking to id and so forth.

I have tried all sort of tricks to achieve this but failed so I have come here wondering if anyone else has had the same issue and found a resolution to it.

Once I have achieved a nested list I can use JavaScript/CSS to function it and make it nice.

If anyone has the solution I need I would be extremely thankful!

Thank you in advance...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Recursive PHP/MySQL list menu

Post by requinix »

There's a sticky called "Useful Posts". Look in there.
deemjay
Forum Newbie
Posts: 4
Joined: Fri Dec 12, 2008 4:13 pm

Re: Recursive PHP/MySQL list menu

Post by deemjay »

Here is the code I am using to create a function that calls a function. I have looked at it until my eyes go cross but I cannot get it to return what I want. The data is in DB ready to go but it just won't go...

Essentially the function calls itself. From the top it begins the function and during the build calls itself. If nothing found first time round it sets parent_id to 0 which they all are at top level. Then it runs the funtion again but this time finds the id. This id is then passed as the parent if you get my drift.

If anyone has had same problem and resolved it please look at the code and tell me where I am lacking.

Thanks...

Code: Select all

 
<?php
require_once('Connections/cms.php');
 
$domain = "andromeda";
 
/* Recursive LIST menu function */
 
function buildMenu($database_cms,$cms,$domain,$parent) {
    
    mysql_select_db($database_cms, $cms);
    $query_menu = "SELECT id, name, parent_id, domain FROM menu WHERE parent_id = '".$parent."' AND domain = '".$domain."'";
    $menu = mysql_query($query_menu, $cms) or die(mysql_error());
    $row_menu = mysql_fetch_assoc($menu);
    $totalRows_menu = mysql_num_rows($menu);
    
    if ($totalRows_menu>0) {
    
        $build .= "<ul>";
        
            while ($row = mysql_fetch_assoc($menu)) {
                
                $build .= "<li>" . $row['name'];
                $build .= buildMenu($database_cms,$cms,$domain,$parent);
                $build .= "</li>";
            
            }
            
        $build .= "</ul>";  
    
    }
 
    return $build;
 
}
 
$menu = buildMenu($database_cms,$cms,$domain,0);
 
echo $menu;
?>
 
cavemaneca
Forum Commoner
Posts: 59
Joined: Sat Dec 13, 2008 2:16 am

Re: Recursive PHP/MySQL list menu

Post by cavemaneca »

Stayed up late working on this for you. Hope it helps. Unfortunately it was really simple. I had it fixed the whole time except for one character ($build = instead of $build .=)
Note: Don't use capital letters in dir or file names unless you have to

Code: Select all

<?php
require_once('connections/cms.php');
  
$domain = "andromeda";
 
/* Recursive LIST menu function */
 
[color=#FF0000]function buildMenu($database_cms,$cms,$domain,$parent,$build,$sp[/color]) {
   
    mysql_select_db($database_cms, $cms);
    $query_menu = "SELECT id, name, parent_id, domain FROM `menu` WHERE `parent_id` = '".$parent."' AND `domain` = '".$domain."'";
    $menu = mysql_query($query_menu, $cms) or die(mysql_error());
        [color=#0000FF]$space = "";[/color]
        [color=#0000FF]for ($i = 1; $i <= $sp * 2; $i++) {[/color]
            [color=#0000FF]$space .= ' ';[/color]
                [color=#0000FF]}[/color]
        [color=#0000FF]$sp++;[/color]
     
    if (mysql_num_rows($menu) > 0) {
        
            [color=#0000FF]$build .= $space."<ul>\n";[/color]
          
            [color=#FF0000]while ($row = mysql_fetch_assoc($menu)) {[/color]
                [color=#FF0000]$build .= $space."  <li>" . $row['name'] . "\n";[/color]
                [color=#FF0000]$build = buildMenu($database_cms,$domain,$row['id'],$build,$sp);[/color] 
                [color=#FF0000]$build .= $space."  </li>\n";[/color]
            }
           
        [color=#FF0000]$build .= $space."</ul>\n"[/color]; 
   
    }
 
    return $build;
}
 
[color=#FF0000]$menu = buildMenu($database_cms,$cms,$domain,0,"",0);[/color]
 
echo $menu;
?>
Notes on code: I set it to pass the current $build to the next round of the function to sort of simplify it. As well, you can start with a partially formed $build sent to the function. The variable $sp pretty much just makes the <html> output look nice.

The ouput html src looks like this

Code: Select all

<ul> 
  <li>First
  <ul> 
    <li>first child
    </li> 
    <li>second child
    <ul> 
      <li>first grand child
      </li> 
    </ul> 
    </li> 
  </ul> 
  </li> 
  <li>Second
  <ul> 
    <li>third child
    </li> 
  </ul> 
  </li> 
  <li>Third
  </li> 
</ul>
Next I removed these on purpose

Code: Select all

    $menu = mysql_query($query_menu, $cms) or die(mysql_error());
    [color=#FF0000]$row_menu = mysql_fetch_assoc($menu);[/color]
    [color=#FF0000]$totalRows_menu = mysql_num_rows($menu)[/color];
First, $totalRows_menu = mysql_num_rows($menu) was redundant. I skipped a step by not creating a variable that was going to be used once when I could just check the result itself.
Then, implementing $row_menu = mysql_fetch_assoc($menu); at this point erased the first set of results from showing in $build because it still pulled row 0, making row 1 the first set display when calling while ($row = mysql_fetch_assoc($menu))
Attachments
menu.rar
Just so you know what I worked with, here is an output of what was in the database in xml (couldn't upload as xml so I changed the extension)
(1.39 KiB) Downloaded 339 times
deemjay
Forum Newbie
Posts: 4
Joined: Fri Dec 12, 2008 4:13 pm

Re: Recursive PHP/MySQL list menu

Post by deemjay »

Thanks ever so much for this! I got it working. Was a missing $cms at line 25 that caused a fail but other than that it has produced what I wanted and I am very thankful to you. Need to make some kind of CSS styling for it now but coding a few to see what looks best. Am sure there's a few more people after such a thing so I will post my final code on here with credit to your good self for clearing the mist from my eyes. Sometimes it pays to get a second opinion and the other really can't code!

Thank you for your help.
anagai
Forum Newbie
Posts: 2
Joined: Sat Feb 14, 2009 2:32 am

Re: Recursive PHP/MySQL list menu

Post by anagai »

You dont need to build a space string. You can make a style for the UL to add left side padding. This will increment nicely for whatever child sub menus there are. The top UL should have a different left padding then the child UL. Here is my complete code below. You just have to add your own db connection and menu table field names.

----------------------------------------------------

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    
    <style>
        ul#menu_top {padding: 0;margin-left: 10px;width: 150px;}
        ul#menu_sub {padding: 0;margin-left: 25px;width: 150px;}
        ul li {padding: 3px 3px 3px 0;list-style-type: disc;list-style-position: inside}
    </style>
</head>
<body>
<?
$db = mysql_connect('localhost','username','password');
mysql_select_db('dbname', $db);
 
function buildMenu($parent,$build) 
{
    global $db;    
     
     $query_menu = "SELECT id, title, parent_id FROM `menu` WHERE `parent_id` = '".$parent."' ORDER BY sortOrder";
     $menu = mysql_query($query_menu, $db) or die(mysql_error());
             
     if (mysql_num_rows($menu) > 0) 
     {
        
             if($parent<1)
                $build .= "<ul id='menu_top'>\n"; //parent ul left padding is different than sub ul padding
             else
                $build .= "<ul id='menu_sub'>\n";
          
             while ($row = mysql_fetch_assoc($menu)) 
             {
                 $build .= "<li>" . $row['title'];
                 $build = buildMenu($row['id'],$build);
                 $build .= "</li>\n";            
             }
            
         $build .= "</ul>\n";
    
     }
  
     return $build;
 }
  
 $menu_str = buildMenu(0,"");
 
 echo $menu_str;
?>
</body>
</html>
anagai
Forum Newbie
Posts: 2
Joined: Sat Feb 14, 2009 2:32 am

Re: Recursive PHP/MySQL list menu

Post by anagai »

This is regarding my previous post. Forgot to mention that you can add a sortOrder integer field to every menu item and can sort the menu however you want. It doesnt have to be sorted only alhpa. Sort each sub menu how you want it. just assign 1,2,3,4 etc...
Post Reply