Page 1 of 1

Recursive PHP/MySQL list menu

Posted: Fri Dec 12, 2008 4:22 pm
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...

Re: Recursive PHP/MySQL list menu

Posted: Fri Dec 12, 2008 4:45 pm
by requinix
There's a sticky called "Useful Posts". Look in there.

Re: Recursive PHP/MySQL list menu

Posted: Sat Dec 13, 2008 2:33 am
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;
?>
 

Re: Recursive PHP/MySQL list menu

Posted: Sat Dec 13, 2008 4:53 am
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))

Re: Recursive PHP/MySQL list menu

Posted: Sat Dec 13, 2008 7:55 am
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.

Re: Recursive PHP/MySQL list menu

Posted: Sat Feb 14, 2009 2:41 am
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>

Re: Recursive PHP/MySQL list menu

Posted: Sat Feb 14, 2009 2:52 am
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...